Oracle Star Schema简析(二)
UARTER_DES
C"='1999-Q2')
6 - access("S"."CUST_ID"="C"."CUST_ID")
7 - filter("C"."CUST_STATE_PROVINCE"='CA')
可以看出上面的SQL语句没有用到star transfermation, 因为star_transformation_enabled=false。执行计划只是普通的JOIN操作。
我来把star_transformation_enabled=TRUE，让他用star transfermation技术执行SQL语句。
SQL> alter system set star_transformation_enabled=true;
System altered.
SQL> col PLAN_PLUS_EXP format a120
SQL> set autotrace traceonly exp;
SQL> col PLAN_PLUS_EXP format a120
SQL> set autotrace traceonly exp;
SELECT ch.channel_class, c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s,times t,customers c,channels ch
WHERE s.time_id = t.time_id AND
s.cust_id = c.cust_id AND
s.channel_id = ch.channel_id AND
c.cust_state_province = 'CA' AND
ch.channel_desc IN ('Internet','Catalog') AND
t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
11 GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 580301883
----------------------------------------------------------
----------------------------------------------------
| Id | Operation | Name | Row
s | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------
----------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 73 | 786 (2)| 00:00:10 | | |
| 1 | TEMP TABLE TRANSFORMATION | |
| | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660B_7E653 |
| | | | | |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 3
83 | 9958 | 332 (2)| 00:00:04 | | |
| 4 | HASH GROUP BY | |
1 | 73 | 455 (2)| 00:00:06 | | |
|* 5 | HASH JOIN | |
1 | 73 | 454 (2)| 00:00:06 | | |
|* 6 | HASH JOIN | |
2 | 104 | 450 (2)| 00:00:06 | | |
|* 7 | HASH JOIN | |
32 | 1184 | 448 (2)| 00:00:06 | | |
|* 8 | TABLE ACCESS FULL | TIMES | 1
83 | 2928 | 15 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE SUBQUERY | | 2
54 | 5334 | 432 (2)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 2
54 | 5334 | 432 (2)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 11 | BITMAP CONVERSION TO ROWIDS | |
| | | | | |
| 12 | BITMAP AND | |
| | | | | |
| 13 | BITMAP MERGE | |
| | | | | |
| 14 | BITMAP KEY ITERATION | |
| | | | | |
| 15 | BUFFER SORT | |
| | | | | |
|* 16 | TABLE ACCESS FULL | CHANNELS |
2 | 42 | 3 (0)| 00:00:01 | | |
|* 17 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX |
| | | |KEY(SQ)|KEY(SQ)|
| 18 | BITMAP MERGE | |
| | | |