In Oracle Data Warehouse (OLAP), MVIEW, Query Rewrite, and Dimension are very important optimization methods, I don't want to repeat the previous two here, mainly to experience the role of dimension. To play the role of a dimension, we still need to use the previous two. below is the simplest example of a dimension table I designed. In addition to connect and resource, database users must also grant Query Rewrite, Create Materialized View, and Create Dimension permissions.
1. Create a maintenance table
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 a fact table
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. generate an intermediate table by month.
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 tables and enable query rewriting at the session level
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. First, let's take a look at the execution plan of the SQL statement summarized by quarter. Theoretically, it can be further summarized and generated from the Summary by month. However, Oracle cannot execute the statement as follows.
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. Create a dimension object to tell Oracle the tree relationship on the four fields in the TIME_DIM table. Without this statement, Oracle considers that the data does not contain this tree relationship.
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. After the maintenance is built, run the SQL statement quarterly or annually. What are the differences between their execution plans?
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 ASQL> SELECT /*+ all_rows */ D.F_YEAR,
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_YEAR
7 / Execute Plan
SQLPLAN COST CARD KBYTE PS PE
------------------------------------------------------------ ---- ---- ----- -- --
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS 10 4 0
1 0 HASH (GROUP BY) 10 4 0
2 1 HASH JOIN 9 97 6
3 2 MAT_VIEW REWRITE ACCESS (FULL) OF MV_FACT_S... 3 34 2
4 2 VIEW OF 5 97 1
5 4 HASH (UNIQUE) 5 97 1
6 5 TABLE ACCESS (FULL) OF TIME_DIM (TABLE) 4 999 11
|
I don't know how to explain it in a language table, so I designed this example to explain it. If you don't understand it, please read it several times!
