转载：http://blog.csdn.net/waterxcfg304/article/details/8524542
星型转换是一个非常强大的优化技术，它是通过对原来的SQL语句的隐式的改写来实现的，它能够很大程度减少I/O. 终端用户并不需要知道有关星型转换的任何细节。数据库优化器会在合适的时候进行星型转换。要获得星型转换的最大性能，需要遵循以下3个基本的条件：
1，事实表上的维度列上要有外键
2，事实表的每个外键上都有BITMAP索引。
3，star_transformation_enabled=true。 系统默认是false. 它有三个取值：（TRUE, FALSE, TEMP_DISABLE）.其中TEMP_DISABLE表示不允许用临时表来存放第一次扫描的结果集。 星型查询中，维度表会被扫描两次，如果维度表很大，性能会很差，所以需要一个临时表来存放第一次扫描的维度表集合。
如果能够满足这三个条件,则查询会使用star transformation,而这是提高基于事实表的查询效率的主要的技术。
数据库进行星型查询时，会使用两个基本的阶段：
第一个阶段从事实表(或者说结果集)里获取所有必要的记录行。由于这是通过bitmap索引来检索数据，因此比较高效。
第二个阶段将该结果集与维度表进行关联。这叫做semi-join(也就是exists和in写法)。
注意：只有oracle企业版才有bitmap索引。标准版不支持bitmap索引和星型转换。
下面的SQL语句执行计划可以很好的说明进行"星型查询"的条件：
SQL> show parameter star_transformation_enabled
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled string FALSE
SQL> conn sh/sh
Connected.
SQL>SELECT ch.channel_class, c.cust_city,
> s.cust_id = c.cust_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: 1612666291
--------------------------------------------------------------------------------
--------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | Pstart| Pstop |
--------------------------------------------------------------------------------
--------------------------
| 0 | SELECT STATEMENT | | 572 | 48048 | 793 (8)|
00:00:10 | | |
| 1 | HASH GROUP BY | | 572 | 48048 | 793 (8)|
00:00:10 | | |
|* 2 | HASH JOIN | | 3116 | 255K| 791 (8)|
00:00:10 | | |
|* 3 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)|
00:00:01 | | |
|* 4 | HASH JOIN | | 6231 | 383K| 788 (8)|
00:00:10 | | |
|* 5 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 15 (0)|
00:00:01 | | |
|* 6 | HASH JOIN | | 49822 | 2286K| 771 (8)|
00:00:10 | | |
|* 7 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 332 (2)|
00:00:04 | | |
| 8 | PARTITION RANGE SUBQUERY| | 918K| 18M| 426 (9)|
00:00:06 |KEY(SQ)|KEY(SQ)|
| 9 | TABLE ACCESS FULL | SALES | 918K| 18M| 426 (9)|
00:00:06 |KEY(SQ)|KEY(SQ)|
--------------------------------------------------------------------------------
--------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -> 3 -> 4 -> 5 -> C"='1999-Q2')
6 - access("S"."CUST_ID"="C"."CUST_ID")
7 ->
可以看出上面的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,
> s.cust_id = c.cust_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 | |
| | | | | |
| 19 | BITMAP KEY ITERATION | |
| | | | | |
| 20 | BUFFER SORT | |
| | | | | |
|* 21 | TABLE ACCESS FULL | TIMES | 1
83 | 2928 | 15 (0)| 00:00:01 | | |
|* 22 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX |
| | | |KEY(SQ)|KEY(SQ)|
| 23 | BITMAP MERGE | |
| | | | | |
| 24 | BITMAP KEY ITERATION | |
| | | | | |
| 25 | BUFFER SORT | |
| | | | | |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_7E653 |
1 | 13 | 2 (0)| 00:00:01 | | |
|* 27 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX |
| | | |KEY(SQ)|KEY(SQ)|
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_7E653 | 3
83 | 5745 | 2 (0)| 00:00:01 | | |
|* 29 | TABLE ACCESS FULL | CHANNELS |
2 | 42 | 3 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C"."CUST_STATE_PROVINCE"='CA')
5 -> 6 -> 7 -> 8 -> C"='1999-Q2')
16 -> 17 -> 21 -> C"='1999-Q2')
22 -> 27 -> 29 ->
从上面执行计划可以看出，此SQL语句做了star transformation. 走了BITMAP索引，例如：BITMAP MERGE ,BITMAP AND等等。
974
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
