oracle创建时间维,Oracle 创建和删除维

--create sales table

CREATE TABLE sales

(trans_date DATE, cust_id INT, sales_amount NUMBER);

--insert sale rowsINSERT /*+ APPEND */

INTO SALES

SELECT TRUNC(SYSDATE, 'year') + MOD(ROWNUM, 366) TRANS_DATE,

MOD(ROWNUM, 100) CUST_ID,

ABS(DBMS_RANDOM.RANDOM) / 100 SALES_AMOUNT

FROM ALL_OBJECTS;

SELECT COUNT(*) FROM sales;

--go on inserting rows

BEGIN

FOR I IN 1 .. 6 LOOP

INSERT /*+ APPEND */

INTO SALES

SELECT TRANS_DATE,

CUST_ID,

ABS(DBMS_RANDOM.RANDOM) / 100 SALES_AMOUNT

FROM SALES;

COMMIT;

END LOOP;

END;

--create index organized table

CREATE TABLE TIME_HIERARCHY(DAY PRIMARY KEY, MMYYYY, MON_YYYY, QTR_YYYY, YYYY) ORGANIZATION INDEX AS

SELECT DISTINCT TRANS_DATE DAY,

CAST(TO_CHAR(TRANS_DATE, 'mmyyyy') AS NUMBER) MMYYYY,

TO_CHAR(TRANS_DATE, 'mon-yyyy') MON_YYYY,

'Q' || CEIL(TO_CHAR(TRANS_DATE, 'mm') / 3) || ' FY' ||

TO_CHAR(TRANS_DATE, 'yyyy') QTR_YYYY,

CAST(TO_CHAR(TRANS_DATE, 'yyyy') AS NUMBER) YYYY

FROM SALES;

--create materialized view

CREATE MATERIALIZED VIEW MV_SALES BUILD IMMEDIATE REFRESH ON DEMAND ENABLE QUERY REWRITE AS

SELECT SALES.CUST_ID,

SUM(SALES.SALES_AMOUNT) SALES_AMOUNT,

TIME_HIERARCHY.MMYYYY

FROM SALES, TIME_HIERARCHY

WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY

GROUP BY SALES.CUST_ID, TIME_HIERARCHY.MMYYYY;

analyze table sales compute statistics;

analyze table time_hierarchy compute statistics;

alter session set query_rewrite_enabled=true;

alter session set query_rewrite_integrity=trusted;

--query by Month

SELECT TIME_HIERARCHY.MMYYYY, SUM(SALES_AMOUNT)

FROM SALES, TIME_HIERARCHY

WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY

GROUP BY TIME_HIERARCHY.MMYYYY

--query by QuarterSELECT TIME_HIERARCHY.QTR_YYYY, SUM(SALES_AMOUNT)

FROM SALES, TIME_HIERARCHY

WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY

GROUP BY TIME_HIERARCHY.QTR_YYYY;

--create dimension

CREATE DIMENSION TIME_HIERARCHY_DIM

LEVEL DAY IS TIME_HIERARCHY.DAY

LEVEL MMYYYY IS TIME_HIERARCHY.MMYYYY

LEVEL QTR_YYYY IS TIME_HIERARCHY.QTR_YYYY

LEVEL YYYY IS TIME_HIERARCHY.YYYY

HIERARCHY TIME_ROLLUP(DAY CHILD OF MMYYYY CHILD OF QTR_YYYY CHILD OF YYYY)

ATTRIBUTE MMYYYY DETERMINES MON_YYYY;

--drop dimension

DROP DIMENSION TIME_HIERARCHY_DIM;

--yearly query

SELECT TIME_HIERARCHY.YYYY, SUM(SALES_AMOUNT)

FROM SALES, TIME_HIERARCHY

WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY

GROUP BY TIME_HIERARCHY.YYYY;

--create index organized table

CREATE TABLE CUSTOMER_HIERARCHY(CUST_ID PRIMARY KEY, ZIP_CODE, REGION) ORGANIZATION INDEX AS

SELECT CUST_ID,

MOD(ROWNUM, 6) || TO_CHAR(MOD(ROWNUM, 1000), 'fm0000') ZIP_CODE,

MOD(ROWNUM, 6) REGION

FROM (SELECT DISTINCT CUST_ID FROM SALES);

analyze table CUSTOMER_HIERARCHY compute statistics;

drop materialized view mv_sales;

--recreate materialized view

CREATE MATERIALIZED VIEW MV_SALES BUILD IMMEDIATE REFRESH ON DEMAND ENABLE QUERY REWRITE AS

SELECT CUSTOMER_HIERARCHY.ZIP_CODE,

TIME_HIERARCHY.MMYYYY,

SUM(SALES.SALES_AMOUNT) SALES_AMOUNT

FROM SALES, TIME_HIERARCHY, CUSTOMER_HIERARCHY

WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY

AND SALES.CUST_ID = CUSTOMER_HIERARCHY.CUST_ID

GROUP BY CUSTOMER_HIERARCHY.ZIP_CODE, TIME_HIERARCHY.MMYYYY;

--query by month

SELECT CUSTOMER_HIERARCHY.ZIP_CODE,

TIME_HIERARCHY.MMYYYY,

SUM(SALES.SALES_AMOUNT) SALES_AMOUNT

FROM SALES, TIME_HIERARCHY, CUSTOMER_HIERARCHY

WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY

AND SALES.CUST_ID = CUSTOMER_HIERARCHY.CUST_ID

GROUP BY CUSTOMER_HIERARCHY.ZIP_CODE, TIME_HIERARCHY.MMYYYY

--query by year

SELECT CUSTOMER_HIERARCHY.REGION,

TIME_HIERARCHY.YYYY,

SUM(SALES.SALES_AMOUNT) SALES_AMOUNT

FROM SALES, TIME_HIERARCHY, CUSTOMER_HIERARCHY

WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY

AND SALES.CUST_ID = CUSTOMER_HIERARCHY.CUST_ID

GROUP BY CUSTOMER_HIERARCHY.REGION, TIME_HIERARCHY.YYYY;

DROP DIMENSION time_hierarchy_dim;

CREATE DIMENSION SALES_DIMENSION

LEVEL CUST_ID IS CUSTOMER_HIERARCHY.CUST_ID

LEVEL ZIP_CODE IS CUSTOMER_HIERARCHY.ZIP_CODE

LEVEL REGION IS CUSTOMER_HIERARCHY.REGION

LEVEL DAY IS TIME_HIERARCHY.DAY

LEVEL MMYYYY IS TIME_HIERARCHY.MMYYYY

LEVEL QTR_YYYY IS TIME_HIERARCHY.QTR_YYYY

LEVEL YYYY IS TIME_HIERARCHY.YYYY

HIERARCHY CUST_ROLLUP(CUST_ID CHILD OF ZIP_CODE CHILD OF REGION)

HIERARCHY TIME_ROLLUP(DAY CHILD OF MMYYYY CHILD OF QTR_YYYY CHILD OF YYYY)

ATTRIBUTE MMYYYY DETERMINES MON_YYYY;

SELECT CUSTOMER_HIERARCHY.REGION,

TIME_HIERARCHY.YYYY,

SUM(SALES.SALES_AMOUNT) SALES_AMOUNT

FROM SALES, TIME_HIERARCHY, CUSTOMER_HIERARCHY

WHERE SALES.TRANS_DATE = TIME_HIERARCHY.DAY

AND SALES.CUST_ID = CUSTOMER_HIERARCHY.CUST_ID

GROUP BY CUSTOMER_HIERARCHY.REGION, TIME_HIERARCHY.YYYY;

--DROP DIMENSION SALES_DIMENSION;

ALTER SYSTEM FLUSH buffer_cache;

ALTER SYSTEM FLUSH SHARED_POOL;

SELECT * FROM dba_dimensions;

SELECT * FROM user_indexes WHERE index_type ='IOT - TOP';

SELECT * FROM user_indextypes;

SELECT * FROM TIME_HIERARCHY;

SELECT * FROM CUSTOMER_HIERARCHY;