Oracle Dimension學習樣本____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;