1、创建一个维护表

CREATE TABLE TIME_DIM
AS
SELECT TO_CHAR(SYSDATE+ROWNUM,'YYYY') F_YEAR,
TO_CHAR(SYSDATE+ROWNUM,'YYYY-Q') F_QUATER,
TO_CHAR(SYSDATE+ROWNUM,'YYYY-MM') F_MONTH,
TRUNC(SYSDATE+ROWNUM,'DD') F_DAY
FROM DBA_OBJECTS
WHERE ROWNUM < 1000;
ALTER TABLE TIME_DIM MODIFY F_YEAR NOT NULL;
ALTER TABLE TIME_DIM MODIFY F_QUATER NOT NULL;
ALTER TABLE TIME_DIM MODIFY F_MONTH NOT NULL;
ALTER TABLE TIME_DIM MODIFY F_DAY NOT NULL;
ALTER TABLE TIME_DIM ADD PRIMARY KEY (F_DAY);
　　2、创建一个事实表

CREATE TABLE FACT_SALES
AS
SELECT TRUNC(SYSDATE+ROWNUM,'DD') F_DAY,
TRUNC(DBMS_RANDOM.VALUE * 10000,2)　M_AMOUNT1,
TRUNC(DBMS_RANDOM.VALUE * 10000,2)　M_AMOUNT2
FROM DBA_OBJECTS
WHERE ROWNUM < 1000;
-- Please execute the following insert multiple times
INSERT INTO FACT_SALES
SELECT F_DAY,
TRUNC(DBMS_RANDOM.VALUE * 10000,2)　M_AMOUNT1,
TRUNC(DBMS_RANDOM.VALUE * 10000,2)　M_AMOUNT2
FROM FACT_SALES
/　
ALTER TABLE FACT_SALES MODIFY F_DAY NOT NULL;
ALTER TABLE FACT_SALES ADD FOREIGN KEY (F_DAY) REFERENCES TIME_DIM(F_DAY);
　　3、将数据按月份进行汇总生成中间表

CREATE MATERIALIZED VIEW MV_FACT_SALES
ENABLE QUERY REWRITE
AS
SELECT D.F_MONTH,
SUM(F.M_AMOUNT1) M_AMOUNT1,
SUM(F.M_AMOUNT2) M_AMOUNT2
FROM TIME_DIM D, FACT_SALES F
WHERE D.F_DAY = F.F_DAY
GROUP BY D.F_MONTH
/
　　4、分析表, 并在会话级启用查询重写

ANALYZE TABLE TIME_DIM COMPUTE STATISTICS;
ANALYZE TABLE FACT_SALES　COMPUTE STATISTICS;
ANALYZE TABLE MV_FACT_SALES COMPUTE STATISTICS;
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
　　5、先来看一下按季汇总的SQL语句的执行计划, 理论上, 可以从按月的汇总中进一步汇总生成, 但这时去执行的话, Oracle并不能这样执行

ASQL> SELECT D.F_QUATER,
2　　　　SUM(F.M_AMOUNT1) M_AMMOUNT1,
3　　　　SUM(F.M_AMOUNT2) M_AMMOUNT2
4 FROM TIME_DIM D, FACT_SALES F
5 WHERE D.F_DAY = F.F_DAY
6 GROUP BY D.F_QUATER
7 /
Execute Plan
SQLPLAN　　　　　　　　　　　　　　　　　　　　　　　　　COST　 CARD KBYTE PS PE
-------------------------------------------------------- ---- ------ ----- -- --
0　　 SELECT STATEMENT Optimizer=ALL_ROWS　　　　　　　 626　　 12　　 1　　　
1　 0　 HASH (GROUP BY)　　　　　　　　　　　　　　　　 626　　 12　　 1　　　
2　 1　　 HASH JOIN　　　　　　　　　　　　　　　　　　 609 287712 16858　　　
3　 2　　　 TABLE ACCESS (FULL) OF TIME_DIM (TABLE)　　　 4　　999　　13　　　
4　 2　　　 TABLE ACCESS (FULL) OF FACT_SALES (TABLE)　 602 287712 13206
　　6、接下来来创建一个维对象, 用来告诉Oracle在TIME_DIM表的四个字段上存在的树状关系, 如果没有这个声明, Oracle会认为数据是不附合这个树状关系的

CREATE DIMENSION TIME_DIM
LEVEL YEAR IS (TIME_DIM.F_YEAR)
LEVEL QUATER IS (TIME_DIM.F_QUATER)
LEVEL MONTH IS (TIME_DIM.F_MONTH)
LEVEL DAY IS (TIME_DIM.F_DAY)
HIERARCHY Y_Q_M_D
(
DAY CHILD OF
MONTH CHILD OF
QUATER CHILD OF　YEAR
)
HIERARCHY Y_M_D
(
DAY CHILD OF
MONTH CHILD OF YEAR
)
/
　　7、建好维后, 重新跑按季度汇总或按年汇总的SQL, 看他们的执行计划有什么不同?

ASQL> SELECT D.F_QUATER,
2　　　　SUM(F.M_AMOUNT1) M_AMMOUNT1,
3　　　　SUM(F.M_AMOUNT2) M_AMMOUNT2
4 FROM TIME_DIM D, FACT_SALES F
5 WHERE D.F_DAY = F.F_DAY
6 GROUP BY D.F_QUATER
7 /
Execute Plan
SQLPLAN　　　　　　　　　　　　　　　　　　　　　　　　　　 COST CARD KBYTE PS PE
----------------------------------------------------------- ---- ---- ----- -- --
0　　 SELECT STATEMENT Optimizer=ALL_ROWS　　　　　　　　　 10　 12　　 1　　　
1　 0　 HASH (GROUP BY)　　　　　　　　　　　　　　　　　　 10　 12　　 1　　　
2　 1　　 HASH JOIN　　　　　　　　　　　　　　　　　　　　　9　289　　17　　　
3　 2　　　 MAT_VIEW REWRITE ACCESS (FULL) OF MV_FACT_...　　3　 34　　 2　　　
4　 2　　　 VIEW OF　　　　　　　　　　　　　　　　　　　　　5　289　　 4　　　
5　 4　　　　 HASH (UNIQUE)　　　　　　　　　　　　　　　　　5　289　　 4　　　
6　 5　　　　　 TABLE ACCESS (FULL) OF TIME_DIM (TABLE)　　　4　999　　13