--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;