Oracle 创建和删除维

--create sales table    
CREATE TABLE sales    
(trans_date DATE, cust_id INT, sales_amount NUMBER);--insert sale rows     INSERT /*+ 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 Quarter     SELECT 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;