当前位置：Oracle研究中心 > 运维DBA > Oracle性能优化 >
【学习笔记】Oracle优化之星形查询star transformation query教程
时间:2016-12-22 22:05 来源:Oracle研究中心 作者:网络 点击: 次
天萃荷净
Oracle研究中心学习笔记：分享一篇关于Oracle star transformation query星形查询的文章这里，我们介绍一下对于星型模式的一些调优思想。我们先来介绍一下关于星型模式的一些知识。
星型模式是数据仓库中极为常见的一种设计模式，它包括一个大的用来存储详细业务数据的事实表（Fact Table），同时包括外键。
这些外键关联到一个相对较小的更静态的维度表（dimenstion table）。在维度表中记录了用来描述事实表中业务数据的一些分类，比如客户、时间、产品等信息。
在OLAP大型数据集中，星型模式是一个实现高性能有效的存储途径。在Oracle及其他一些商业数据库当中都做了重要的优化，
星型模式的一种特殊形式称为雪花模式。
雪花模式是指维度表本身也包括外健，用以关联其他更高一级的维度表或是其他数据表。它是一种常见的模式，能够合理地满足一些数据仓库的需求。
下面，我们以Oracle示例模式中的SH下的表为例，来看一下星型模式。
Oracle 星型模式的调优
我把SH模式下的销售（sale）相关的业务表画了一个图，中间的要SALES事实表中的销售数据（QUANTITY_SOLD）、销售金额（AMOUNT_SOLD）
是根据各个时间段、产品、客户、 销售渠道和促销聚合而来的。
通过关联时间维度表（TIMES）、产品维度表（PRODUCTS）、客户维度表（CUSTOMERS）、渠道维度表（CHANNELS）和促销活动维度表（PROMOTIONS），
我们可以得到事实表中每个聚集的详细描述。
下面，我们来看一个查询。
sh@ORA11> select quantity_sold,amount_sold
2 from times t join sales s on t.time_id = s.time_id
3 join products p1 on s.prod_id = p1.prod_id
4 join customers c1 on s.cust_id = c1.cust_id
5 join channels c2 on s.channel_id = c2.channel_id
6 join promotions p2 on s.promo_id = p2.promo_id
7 where c1.cust_first_name = 'Ramsay'
8 and c1.cust_last_name = 'Alden'
9 and t.day_name = 'Saturday'
10 and c2.channel_class = 'Direct'
11 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'
12 and p2.promo_name = 'NO PROMOTION #'
13 /
QUANTITY_SOLD AMOUNT_SOLD
------------- -----------
1 1264.4
sh@ORA11>
Oracle处理这种星型连接的默认方法是查询所有的维度表来检索与WHERE条件相关的外键值，然后使用全笛卡尔连接合并这些结果集，
产生的外键被用于识别事实表的记录。如果事实表中对外键值上有合理的索引，那么它可以被用来优化最后一步。
为了便于分析，下面，我把表SALES上所创建的索引进行一个删除。
sh@ORA11> begin
2 for i in (select index_name
3 from user_indexes
4 where table_name = 'SALES')
5 loop
6 execute immediate 'drop index ' || i.index_name;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
sh@ORA11>
删除完成。
在事实表没有索引的情况下，我们来看一下上述查询的执行计划。
sh@ORA11> set autotrace traceonly explain
sh@ORA11> select quantity_sold,amount_sold
2 from times t join sales s on t.time_id = s.time_id
3 join products p1 on s.prod_id = p1.prod_id
4 join customers c1 on s.cust_id = c1.cust_id
5 join channels c2 on s.channel_id = c2.channel_id
6 join promotions p2 on s.promo_id = p2.promo_id
7 where c1.cust_first_name = 'Ramsay'
8 and c1.cust_last_name = 'Alden'
9 and t.day_name = 'Saturday'
10 and c2.channel_class = 'Direct'
11 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'
12 and p2.promo_name = 'NO PROMOTION #'
13 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2561703891
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 136 | 776 (2)| 00:00:10 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 136 | 776 (2)| 00:00:10 | | |
|* 3 | HASH JOIN | | 241 | 27956 | 535 (3)| 00:00:07 | | |
|* 4 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | 3 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 578 | 60690 | 532 (3)| 00:00:07 | | |
|* 6 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29 | 17 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 2312 | 171K| 514 (3)| 00:00:07 | | |
| 8 | PART JOIN FILTER CREATE | :BF0000 | 261 | 4437 | 18 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | TIMES | 261 | 4437 | 18 (0)| 00:00:01 | | |
|* 10 | HASH JOIN | | 12941 | 745K| 496 (3)| 00:00:06 | | |
|* 11 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 3 (0)| 00:00:01 | | |
| 12 | PARTITION RANGE JOIN-FILTER| | 918K| 25M| 489 (2)| 00:00:06 |:BF0000|:BF0000|
| 13 | TABLE ACCESS FULL | SALES | 918K| 25M| 489 (2)| 00:00:06 |:BF0000|:BF0000|
|* 14 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 20 | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."CHANNEL_ID"="C2"."CHANNEL_ID")
4 - filter("C2"."CHANNEL_CLASS"='Direct')
5 - access("S"."PROMO_ID"="P2"."PROMO_ID")
6 - filter("P2"."PROMO_NAME"='NO PROMOTION #')
7 - access("T"."TIME_ID"="S"."TIME_ID")
9 - filter("T"."DAY_NAME"='Saturday')
10 - access("S"."PROD_ID"="P1"."PROD_ID")
11 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')
14 - access("S"."CUST_ID"="C1"."CUST_ID")
15 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')
sh@ORA11> set autotrace off
sh@ORA11>
对事实表的外键值创建索引。
sh@ORA11> create index sale_idx01 on sales(prod_id,cust_id,time_id,channel_id,promo_id)
2 /
Index created.
sh@ORA11> exec dbms_stats.gather_table_stats(user,'sales',cascade=>true)
PL/SQL procedure successfully completed.
sh@ORA11>
我们再来看一下上述查询的执行计划。
Execution Plan
----------------------------------------------------------
Plan hash value: 1244022574
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 136 | 489 (1)| 00:00:06 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 136 | 489 (1)| 00:00:06 | | |
| 3 | MERGE JOIN CARTESIAN | | 21 | 2247 | 446 (1)| 00:00:06 | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 90 | 428 (1)| 00:00:06 | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 79 | 425 (1)| 00:00:06 | | |
| 6 | MERGE JOIN CARTESIAN | | 1 | 59 | 20 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 3 (0)| 00:00:01 | | |
| 8 | BUFFER SORT | | 1 | 29 | 17 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29 | 17 (0)| 00:00:01 | | |
| 10 | BUFFER SORT | | 1 | 20 | 408 (1)| 00:00:05 | | |
|* 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 20 | 405 (1)| 00:00:05 | | |
| 12 | BUFFER SORT | | 2 | 22 | 23 (0)| 00:00:01 | | |
|* 13 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | 3 (0)| 00:00:01 | | |
| 14 | BUFFER SORT | | 261 | 4437 | 443 (1)| 00:00:06 | | |
|* 15 | TABLE ACCESS FULL | TIMES | 261 | 4437 | 18 (0)| 00:00:01 | | |
|* 16 | INDEX RANGE SCAN | SALE_IDX01 | 1 | | 2 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 1 | 29 | 3 (0)| 00:00:01 | ROWID | ROWID |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')
9 - filter("P2"."PROMO_NAME"='NO PROMOTION #')
11 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')
13 - filter("C2"."CHANNEL_CLASS"='Direct')
15 - filter("T"."DAY_NAME"='Saturday')
16 - access("S"."PROD_ID"="P1"."PROD_ID" AND "S"."CUST_ID"="C1"."CUST_ID" AND
"T"."TIME_ID"="S"."TIME_ID" AND "S"."CHANNEL_ID"="C2"."CHANNEL_ID"
AND "S"."PROMO_ID"="P2"."PROMO_ID")
对维度表PRODUCTS、PROMOTIONS、CUSTOMERS 、CHANNELS 、TIMES的笛卡尔合并连接（ID为3、4、5、5）定位出外键值的组合。
它们被提供给组合索引进行索引区间查找（ID为16），以检索表SALES中的记录。
对于上述SQL，我们使用10053事件来看一下优化器进行优化重写后的SQL是个什么样子的。
SELECT /*+ OPT_ESTIMATE (TABLE "C1" MIN=16.000000 )
OPT_ESTIMATE (INDEX_SCAN "C1" "CUSTOMERS_PK" MIN=518.000000 )
OPT_ESTIMATE (INDEX_FILTER "C1" "CUSTOMERS_PK" MIN=518.000000 )
OPT_ESTIMATE (INDEX_SCAN "S" "SALE_IDX01" MIN=1.000000 )
OPT_ESTIMATE (INDEX_FILTER "S" "SALE_IDX01" MIN=1.000000 )
OPT_ESTIMATE (TABLE "S" ROWS=1.000000 ) */
"S"."QUANTITY_SOLD" "QUANTITY_SOLD", "S"."AMOUNT_SOLD" "AMOUNT_SOLD"
FROM "SH"."TIMES" "T",
"SH"."SALES" "S",
"SH"."PRODUCTS" "P1",
"SH"."CUSTOMERS" "C1",
"SH"."CHANNELS" "C2",
"SH"."PROMOTIONS" "P2"
WHERE "C1"."CUST_FIRST_NAME" = 'Ramsay'
AND "C1"."CUST_LAST_NAME" = 'Alden'
AND "T"."DAY_NAME" = 'Saturday'
AND "C2"."CHANNEL_CLASS" = 'Direct'
AND "P1"."PROD_NAME" = '17" LCD w/built-in HDTV Tuner'
AND "P2"."PROMO_NAME" = 'NO PROMOTION #'
AND "S"."PROMO_ID" = "P2"."PROMO_ID"
AND "S"."CHANNEL_ID" = "C2"."CHANNEL_ID"
AND "S"."CUST_ID" = "C1"."CUST_ID"
AND "S"."PROD_ID" = "P1"."PROD_ID"
AND "T"."TIME_ID" = "S"."TIME_ID"
按这个方法执行星型连接是高效的，因为它减少了对大事实表的访问。但是也存在一个较严重的缺陷，因为星型模式通常用在数据仓库下，
而对于数据仓库又存在大量不同的where子句，这也就意味着需要大量不同的组合索引。
用于星型查询的笛卡尔连接方法在面临更复杂的模式中是查询时有可能失败，比如如下两个问题。
如果在维度表中匹配的记录数很多，笛卡尔集可能产生很大的结果集；
需要支持所有可能的维度健的组合的组合索引，创建所有这些索引可能不实际。
为了解决这些问题，Oracle提供了星型转化这种优化方法。
星型转化利用事实表上的位图索引来产生一个优化的执行计划。
来处理维度表过多或是无法为所有可能的查询创建组合索引的查询。下面，我们对上述的查询创建位图索引，并查看相应的执行计划。
sh@ORA11> drop index sale_idx01;
Index dropped.
sh@ORA11> create bitmap index sales_prod_bi on sales(prod_id) local;
sh@ORA11> create bitmap index sales_cust_bi on sales(cust_id) local;
sh@ORA11> create bitmap index sales_time_bi on sales(time_id) local;
sh@ORA11> create bitmap index sales_channel_bi on sales(channel_id) local;
sh@ORA11> create bitmap index sales_promo_bi on sales(promo_id) local;
Index created.
sh@ORA11> exec dbms_stats.gather_table_stats(user,'sales',cascade=>true);
PL/SQL procedure successfully completed.
执行上述查询SQL。
sh@ORA11> set autotrace traceonly explain
sh@ORA11> select quantity_sold,amount_sold
2 from times t join sales s on t.time_id = s.time_id
3 join products p1 on s.prod_id = p1.prod_id
4 join customers c1 on s.cust_id = c1.cust_id
5 join channels c2 on s.channel_id = c2.channel_id
6 join promotions p2 on s.promo_id = p2.promo_id
7 where c1.cust_first_name = 'Ramsay'
8 and c1.cust_last_name = 'Alden'
9 and t.day_name = 'Saturday'
10 and c2.channel_class = 'Direct'
11 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'
12 and p2.promo_name = 'NO PROMOTION #'
13 /
Execution Plan
----------------------------------------------------------
Plan hash value: 815725874
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 136| 703 (1)| 00:00:09 | | |
| 1 | NESTED LOOPS | | || | | | |
| 2 | NESTED LOOPS | | 1 | 136| 703 (1)| 00:00:09 | | |
|* 3 | HASH JOIN | | 241 | 27956| 462 (1)| 00:00:06 | | |
|* 4 | TABLE ACCESS FULL | CHANNELS | 2 | 22| 3 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 578 | 60690| 458 (1)| 00:00:06 | | |
|* 6 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29| 17 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 2312 | 171K| 441 (1)| 00:00:06 | | |
|* 8 | TABLE ACCESS FULL | TIMES | 261 | 4437| 18 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | || | | | |
| 10 | NESTED LOOPS | | 12941 | 745K| 422 (0)| 00:00:06 | | |
|* 11 | TABLE ACCESS FULL | PRODUCTS | 1 | 3| 3 (0)| 00:00:01 | | |
| 12 | PARTITION RANGE ALL | | || | | 1 | 28 |
| 13 | BITMAP CONVERSION TO ROWIDS | | || | | | |
|* 14 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BI | || | | 1 | 28 |
| 15 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 361K| 422 (0)| 00:00:06 | 1 | 1 |
|* 16 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 || 0 (0)| 00:00:01 | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 20| 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."CHANNEL_ID"="C2"."CHANNEL_ID")
4 - filter("C2"."CHANNEL_CLASS"='Direct')
5 - access("S"."PROMO_ID"="P2"."PROMO_ID")
6 - filter("P2"."PROMO_NAME"='NO PROMOTION #')
7 - access("T"."TIME_ID"="S"."TIME_ID")
8 - filter("T"."DAY_NAME"='Saturday')
11 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')
14 - access("S"."PROD_ID"="P1"."PROD_ID")
16 - access("S"."CUST_ID"="C1"."CUST_ID")
17 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')
sh@ORA11> set autotrace off
从执行计划来看，它没有实现我们想要实现的执行计划。
有一个问题，要注意一下。对于星型转化，需要设置参数star_transformation_enabled为true，然后在SQL中加入”star_transformation“hint，
或是在SQL中加入“opt_param('star_transformation_enabled',true) star_transformation ”这样的hint。如下：
sh@ORA11> set autotrace traceonly explain
sh@ORA11> select /*+opt_param('star_transformation_enabled','true')
2 star_transformation*/
3 quantity_sold,amount_sold
4 from times t join sales s on t.time_id = s.time_id
5 join products p1 on s.prod_id = p1.prod_id
6 join customers c1 on s.cust_id = c1.cust_id
7 join channels c2 on s.channel_id = c2.channel_id
8 join promotions p2 on s.promo_id = p2.promo_id
9 where c1.cust_first_name = 'Ramsay'
10 and c1.cust_last_name = 'Alden'
11 and t.day_name = 'Saturday'
12 and c2.channel_class = 'Direct'
13 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'
14 and p2.promo_name = 'NO PROMOTION #'
15 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1748952924
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 877 (1)| 00:00:11 | | |
|* 1 | HASH JOIN | | 1 | 46 | 450 (1)| 00:00:06 | | |
| 2 | PARTITION RANGE SUBQUERY | | | 5 | 431 (1)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | | 5 | 431 (1)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 5 | BITMAP AND | | | | | | | |
| 6 | BITMAP MERGE | | | | | | | |
| 7 | BITMAP KEY ITERATION | | | | | | | |
| 8 | BUFFER SORT | | | | | | | |
|* 9 | TABLE ACCESS FULL | CUSTOMERS | 1 | 20 | 405 (1)| 00:00:05 | | |
|* 10 | BITMAP INDEX RANGE SCAN | SALES_CUST_BI | | | | |KEY(SQ)|KEY(SQ)|
| 11 | BITMAP MERGE | | | | | | | |
| 12 | BITMAP KEY ITERATION | | | | | | | |
| 13 | BUFFER SORT | | | | | | | |
|* 14 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 3 (0)| 00:00:01 | | |
|* 15 | BITMAP INDEX RANGE SCAN | SALES_PROD_BI | | | | |KEY(SQ)|KEY(SQ)|
| 16 | BITMAP MERGE | | | | | | | |
| 17 | BITMAP KEY ITERATION | | | | | | | |
| 18 | BUFFER SORT | | | | | | | |
|* 19 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | 3 (0)| 00:00:01 | | |
|* 20 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BI | | | | |KEY(SQ)|KEY(SQ)|
| 21 | BITMAP MERGE | | | | | | | |
| 22 | BITMAP KEY ITERATION | | | | | | | |
| 23 | BUFFER SORT | | | | | | | |
|* 24 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29 | 17 (0)| 00:00:01 | | |
|* 25 | BITMAP INDEX RANGE SCAN | SALES_PROMO_BI | | | | |KEY(SQ)|KEY(SQ)|
|* 26 | TABLE ACCESS FULL | TIMES | 261 | 4437 | 18 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."TIME_ID"="S"."TIME_ID")
9 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')
10 - access("S"."CUST_ID"="C1"."CUST_ID")
14 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')
15 - access("S"."PROD_ID"="P1"."PROD_ID")
19 - filter("C2"."CHANNEL_CLASS"='Direct')
20 - access("S"."CHANNEL_ID"="C2"."CHANNEL_ID")
24 - filter("P2"."PROMO_NAME"='NO PROMOTION #')
25 - access("S"."PROMO_ID"="P2"."PROMO_ID")
26 - filter("T"."DAY_NAME"='Saturday')
Note
-----
- star transformation used for this statement
sh@ORA11> set autotrace off
这里有一个问题要注意一下，星型转化并不比对事实表添加组合索引的方式得到的性能更好。
然而，在实际当中，我们无法为所有可能的查询创建足够的组合索引。但是，通过星型转化，我们只需要在每个外键上创建一个位图索引以支持WHERE子句中可能存在的组合。
当然，我们对于上述的SQL可以进行一步优化，比较为客户名、产品名称创建索引等。下面，我们来看一个相对较简单的SQL。
sh@ORA11> set autotrace traceonly explain
sh@ORA11> select /*+opt_param('star_transformation_enabled','true')
2 star_transformation*/
3 quantity_sold,amount_sold
4 from sales s join products p1 on s.prod_id = p1.prod_id
5 join customers c1 on s.cust_id = c1.cust_id
6 where c1.cust_first_name = 'Ramsay'
7 and c1.cust_last_name = 'Alden'
8 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'
9 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2220034904
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 |12 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 2 | 39 | 7 (0)| 00:00:01 | 1 | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 2 | 39 | 7 (0)| 00:00:01 | 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 4 | BITMAP AND | | | | | | | |
| 5 | BITMAP MERGE | | | | | | | |
| 6 | BITMAP KEY ITERATION | | | | | | | |
| 7 | BUFFER SORT | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 10 | 200 | 3 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | CUST_NAME_IDX | 10 | | 1 (0)| 00:00:01 | | |
|* 10 | BITMAP INDEX RANGE SCAN | SALES_CUST_BI | | | | | 1 | 28 |
| 11 | BITMAP MERGE | | | | | | | |
| 12 | BITMAP KEY ITERATION | | | | | | | |
| 13 | BUFFER SORT | | | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 30 | 2 (0)| 00:00:01 | | |
|* 15 | INDEX RANGE SCAN | PROD_NAME_IDX | 1 | | 1 (0)| 00:00:01 | | |
|* 16 | BITMAP INDEX RANGE SCAN | SALES_PROD_BI | | | | | 1 | 28 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')
10 - access("S"."CUST_ID"="C1"."CUST_ID")
15 - access("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')
16 - access("S"."PROD_ID"="P1"."PROD_ID")
Note
-----
- star transformation used for this statement
sh@ORA11> set autotrace off
上述的索引，我们创建的传统的B*树索引。对于星型转化，我们可以位图连接索引来进一步的优化，如下。
sh@ORA11> create bitmap index sales_prod_bjix on sales(p.prod_name)
2 from sales s,products p
3 where s.prod_id = p.prod_id
4 local
5 /
Index created.
sh@ORA11> create bitmap index sales_cust_bjix on sales(c.cust_first_name,c.cust_last_name)
2 from sales s,customers c
3 where s.cust_id = c.cust_id
4 local
5 /
Index created.
sh@ORA11>
在上述，我们创建了两个位图连接索引，下面，我们来看查询。
sh@ORA11> set autotrace traceonly explain
sh@ORA11> select quantity_sold,amount_sold
2 from sales s join products p on s.prod_id = p.prod_id
3 join customers c on s.cust_id = c.cust_id
4 where c.cust_first_name = 'Ramsay'
5 and c.cust_last_name = 'Alden'
6 and p.prod_name = '17" LCD w/built-in HDTV Tuner'
7 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3815036790
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 386 | 6562 | 59 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 386 | 6562 | 59 (0)| 00:00:01 | 1 | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 386 | 6562 | 59 (0)| 00:00:01 | 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 4 | BITMAP AND | | | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BJIX | | | | | 1 | 28 |
|* 6 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BJIX | | | | | 1 | 28 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("S"."SYS_NC00009$"='Ramsay' AND "S"."SYS_NC00010$"='Alden')
6 - access("S"."SYS_NC00008$"='17" LCD w/built-in HDTV Tuner')
sh@ORA11> set autotrace off
Oracle研究中心
星型模式是数据仓库中极为常见的一种设计模式，它包括一个大的用来存储详细业务数据的事实表（Fact Table），同时包括外键。
这些外键关联到一个相对较小的更静态的维度表（dimenstion table）。在维度表中记录了用来描述事实表中业务数据的一些分类，比如客户、时间、产品等信息。
在OLAP大型数据集中，星型模式是一个实现高性能有效的存储途径。在Oracle及其他一些商业数据库当中都做了重要的优化，
星型模式的一种特殊形式称为雪花模式。
雪花模式是指维度表本身也包括外健，用以关联其他更高一级的维度表或是其他数据表。它是一种常见的模式，能够合理地满足一些数据仓库的需求。
下面，我们以Oracle示例模式中的SH下的表为例，来看一下星型模式。
Oracle 星型模式的调优
我把SH模式下的销售（sale）相关的业务表画了一个图，中间的要SALES事实表中的销售数据（QUANTITY_SOLD）、销售金额（AMOUNT_SOLD）
是根据各个时间段、产品、客户、 销售渠道和促销聚合而来的。
通过关联时间维度表（TIMES）、产品维度表（PRODUCTS）、客户维度表（CUSTOMERS）、渠道维度表（CHANNELS）和促销活动维度表（PROMOTIONS），
我们可以得到事实表中每个聚集的详细描述。
下面，我们来看一个查询。
sh@ORA11> select quantity_sold,amount_sold
2 from times t join sales s on t.time_id = s.time_id
3 join products p1 on s.prod_id = p1.prod_id
4 join customers c1 on s.cust_id = c1.cust_id
5 join channels c2 on s.channel_id = c2.channel_id
6 join promotions p2 on s.promo_id = p2.promo_id
7 where c1.cust_first_name = 'Ramsay'
8 and c1.cust_last_name = 'Alden'
9 and t.day_name = 'Saturday'
10 and c2.channel_class = 'Direct'
11 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'
12 and p2.promo_name = 'NO PROMOTION #'
13 /
QUANTITY_SOLD AMOUNT_SOLD
------------- -----------
1 1264.4
sh@ORA11>
Oracle处理这种星型连接的默认方法是查询所有的维度表来检索与WHERE条件相关的外键值，然后使用全笛卡尔连接合并这些结果集，
产生的外键被用于识别事实表的记录。如果事实表中对外键值上有合理的索引，那么它可以被用来优化最后一步。
为了便于分析，下面，我把表SALES上所创建的索引进行一个删除。
sh@ORA11> begin
2 for i in (select index_name
3 from user_indexes
4 where table_name = 'SALES')
5 loop
6 execute immediate 'drop index ' || i.index_name;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
sh@ORA11>
删除完成。
在事实表没有索引的情况下，我们来看一下上述查询的执行计划。
sh@ORA11> set autotrace traceonly explain
sh@ORA11> select quantity_sold,amount_sold
2 from times t join sales s on t.time_id = s.time_id
3 join products p1 on s.prod_id = p1.prod_id
4 join customers c1 on s.cust_id = c1.cust_id
5 join channels c2 on s.channel_id = c2.channel_id
6 join promotions p2 on s.promo_id = p2.promo_id
7 where c1.cust_first_name = 'Ramsay'
8 and c1.cust_last_name = 'Alden'
9 and t.day_name = 'Saturday'
10 and c2.channel_class = 'Direct'
11 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'
12 and p2.promo_name = 'NO PROMOTION #'
13 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2561703891
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 136 | 776 (2)| 00:00:10 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 136 | 776 (2)| 00:00:10 | | |
|* 3 | HASH JOIN | | 241 | 27956 | 535 (3)| 00:00:07 | | |
|* 4 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | 3 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 578 | 60690 | 532 (3)| 00:00:07 | | |
|* 6 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29 | 17 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 2312 | 171K| 514 (3)| 00:00:07 | | |
| 8 | PART JOIN FILTER CREATE | :BF0000 | 261 | 4437 | 18 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | TIMES | 261 | 4437 | 18 (0)| 00:00:01 | | |
|* 10 | HASH JOIN | | 12941 | 745K| 496 (3)| 00:00:06 | | |
|* 11 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 3 (0)| 00:00:01 | | |
| 12 | PARTITION RANGE JOIN-FILTER| | 918K| 25M| 489 (2)| 00:00:06 |:BF0000|:BF0000|
| 13 | TABLE ACCESS FULL | SALES | 918K| 25M| 489 (2)| 00:00:06 |:BF0000|:BF0000|
|* 14 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 20 | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."CHANNEL_ID"="C2"."CHANNEL_ID")
4 - filter("C2"."CHANNEL_CLASS"='Direct')
5 - access("S"."PROMO_ID"="P2"."PROMO_ID")
6 - filter("P2"."PROMO_NAME"='NO PROMOTION #')
7 - access("T"."TIME_ID"="S"."TIME_ID")
9 - filter("T"."DAY_NAME"='Saturday')
10 - access("S"."PROD_ID"="P1"."PROD_ID")
11 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')
14 - access("S"."CUST_ID"="C1"."CUST_ID")
15 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')
sh@ORA11> set autotrace off
sh@ORA11>
对事实表的外键值创建索引。
sh@ORA11> create index sale_idx01 on sales(prod_id,cust_id,time_id,channel_id,promo_id)
2 /
Index created.
sh@ORA11> exec dbms_stats.gather_table_stats(user,'sales',cascade=>true)
PL/SQL procedure successfully completed.
sh@ORA11>
我们再来看一下上述查询的执行计划。
Execution Plan
----------------------------------------------------------
Plan hash value: 1244022574
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 136 | 489 (1)| 00:00:06 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 136 | 489 (1)| 00:00:06 | | |
| 3 | MERGE JOIN CARTESIAN | | 21 | 2247 | 446 (1)| 00:00:06 | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 90 | 428 (1)| 00:00:06 | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 79 | 425 (1)| 00:00:06 | | |
| 6 | MERGE JOIN CARTESIAN | | 1 | 59 | 20 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 3 (0)| 00:00:01 | | |
| 8 | BUFFER SORT | | 1 | 29 | 17 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29 | 17 (0)| 00:00:01 | | |
| 10 | BUFFER SORT | | 1 | 20 | 408 (1)| 00:00:05 | | |
|* 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 20 | 405 (1)| 00:00:05 | | |
| 12 | BUFFER SORT | | 2 | 22 | 23 (0)| 00:00:01 | | |
|* 13 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | 3 (0)| 00:00:01 | | |
| 14 | BUFFER SORT | | 261 | 4437 | 443 (1)| 00:00:06 | | |
|* 15 | TABLE ACCESS FULL | TIMES | 261 | 4437 | 18 (0)| 00:00:01 | | |
|* 16 | INDEX RANGE SCAN | SALE_IDX01 | 1 | | 2 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 1 | 29 | 3 (0)| 00:00:01 | ROWID | ROWID |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')
9 - filter("P2"."PROMO_NAME"='NO PROMOTION #')
11 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')
13 - filter("C2"."CHANNEL_CLASS"='Direct')
15 - filter("T"."DAY_NAME"='Saturday')
16 - access("S"."PROD_ID"="P1"."PROD_ID" AND "S"."CUST_ID"="C1"."CUST_ID" AND
"T"."TIME_ID"="S"."TIME_ID" AND "S"."CHANNEL_ID"="C2"."CHANNEL_ID"
AND "S"."PROMO_ID"="P2"."PROMO_ID")
对维度表PRODUCTS、PROMOTIONS、CUSTOMERS 、CHANNELS 、TIMES的笛卡尔合并连接（ID为3、4、5、5）定位出外键值的组合。
它们被提供给组合索引进行索引区间查找（ID为16），以检索表SALES中的记录。
对于上述SQL，我们使用10053事件来看一下优化器进行优化重写后的SQL是个什么样子的。
SELECT /*+ OPT_ESTIMATE (TABLE "C1" MIN=16.000000 )
OPT_ESTIMATE (INDEX_SCAN "C1" "CUSTOMERS_PK" MIN=518.000000 )
OPT_ESTIMATE (INDEX_FILTER "C1" "CUSTOMERS_PK" MIN=518.000000 )
OPT_ESTIMATE (INDEX_SCAN "S" "SALE_IDX01" MIN=1.000000 )
OPT_ESTIMATE (INDEX_FILTER "S" "SALE_IDX01" MIN=1.000000 )
OPT_ESTIMATE (TABLE "S" ROWS=1.000000 ) */
"S"."QUANTITY_SOLD" "QUANTITY_SOLD", "S"."AMOUNT_SOLD" "AMOUNT_SOLD"
FROM "SH"."TIMES" "T",
"SH"."SALES" "S",
"SH"."PRODUCTS" "P1",
"SH"."CUSTOMERS" "C1",
"SH"."CHANNELS" "C2",
"SH"."PROMOTIONS" "P2"
WHERE "C1"."CUST_FIRST_NAME" = 'Ramsay'
AND "C1"."CUST_LAST_NAME" = 'Alden'
AND "T"."DAY_NAME" = 'Saturday'
AND "C2"."CHANNEL_CLASS" = 'Direct'
AND "P1"."PROD_NAME" = '17" LCD w/built-in HDTV Tuner'
AND "P2"."PROMO_NAME" = 'NO PROMOTION #'
AND "S"."PROMO_ID" = "P2"."PROMO_ID"
AND "S"."CHANNEL_ID" = "C2"."CHANNEL_ID"
AND "S"."CUST_ID" = "C1"."CUST_ID"
AND "S"."PROD_ID" = "P1"."PROD_ID"
AND "T"."TIME_ID" = "S"."TIME_ID"
按这个方法执行星型连接是高效的，因为它减少了对大事实表的访问。但是也存在一个较严重的缺陷，因为星型模式通常用在数据仓库下，
而对于数据仓库又存在大量不同的where子句，这也就意味着需要大量不同的组合索引。
用于星型查询的笛卡尔连接方法在面临更复杂的模式中是查询时有可能失败，比如如下两个问题。
如果在维度表中匹配的记录数很多，笛卡尔集可能产生很大的结果集；
需要支持所有可能的维度健的组合的组合索引，创建所有这些索引可能不实际。
为了解决这些问题，Oracle提供了星型转化这种优化方法。
星型转化利用事实表上的位图索引来产生一个优化的执行计划。
来处理维度表过多或是无法为所有可能的查询创建组合索引的查询。下面，我们对上述的查询创建位图索引，并查看相应的执行计划。
sh@ORA11> drop index sale_idx01;
Index dropped.
sh@ORA11> create bitmap index sales_prod_bi on sales(prod_id) local;
sh@ORA11> create bitmap index sales_cust_bi on sales(cust_id) local;
sh@ORA11> create bitmap index sales_time_bi on sales(time_id) local;
sh@ORA11> create bitmap index sales_channel_bi on sales(channel_id) local;
sh@ORA11> create bitmap index sales_promo_bi on sales(promo_id) local;
Index created.
sh@ORA11> exec dbms_stats.gather_table_stats(user,'sales',cascade=>true);
PL/SQL procedure successfully completed.
执行上述查询SQL。
sh@ORA11> set autotrace traceonly explain
sh@ORA11> select quantity_sold,amount_sold
2 from times t join sales s on t.time_id = s.time_id
3 join products p1 on s.prod_id = p1.prod_id
4 join customers c1 on s.cust_id = c1.cust_id
5 join channels c2 on s.channel_id = c2.channel_id
6 join promotions p2 on s.promo_id = p2.promo_id
7 where c1.cust_first_name = 'Ramsay'
8 and c1.cust_last_name = 'Alden'
9 and t.day_name = 'Saturday'
10 and c2.channel_class = 'Direct'
11 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'
12 and p2.promo_name = 'NO PROMOTION #'
13 /
Execution Plan
----------------------------------------------------------
Plan hash value: 815725874
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 136| 703 (1)| 00:00:09 | | |
| 1 | NESTED LOOPS | | || | | | |
| 2 | NESTED LOOPS | | 1 | 136| 703 (1)| 00:00:09 | | |
|* 3 | HASH JOIN | | 241 | 27956| 462 (1)| 00:00:06 | | |
|* 4 | TABLE ACCESS FULL | CHANNELS | 2 | 22| 3 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 578 | 60690| 458 (1)| 00:00:06 | | |
|* 6 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29| 17 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 2312 | 171K| 441 (1)| 00:00:06 | | |
|* 8 | TABLE ACCESS FULL | TIMES | 261 | 4437| 18 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | || | | | |
| 10 | NESTED LOOPS | | 12941 | 745K| 422 (0)| 00:00:06 | | |
|* 11 | TABLE ACCESS FULL | PRODUCTS | 1 | 3| 3 (0)| 00:00:01 | | |
| 12 | PARTITION RANGE ALL | | || | | 1 | 28 |
| 13 | BITMAP CONVERSION TO ROWIDS | | || | | | |
|* 14 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BI | || | | 1 | 28 |
| 15 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 361K| 422 (0)| 00:00:06 | 1 | 1 |
|* 16 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 || 0 (0)| 00:00:01 | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 20| 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."CHANNEL_ID"="C2"."CHANNEL_ID")
4 - filter("C2"."CHANNEL_CLASS"='Direct')
5 - access("S"."PROMO_ID"="P2"."PROMO_ID")
6 - filter("P2"."PROMO_NAME"='NO PROMOTION #')
7 - access("T"."TIME_ID"="S"."TIME_ID")
8 - filter("T"."DAY_NAME"='Saturday')
11 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')
14 - access("S"."PROD_ID"="P1"."PROD_ID")
16 - access("S"."CUST_ID"="C1"."CUST_ID")
17 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')
sh@ORA11> set autotrace off
从执行计划来看，它没有实现我们想要实现的执行计划。
有一个问题，要注意一下。对于星型转化，需要设置参数star_transformation_enabled为true，然后在SQL中加入”star_transformation“hint，
或是在SQL中加入“opt_param('star_transformation_enabled',true) star_transformation ”这样的hint。如下：
sh@ORA11> set autotrace traceonly explain
sh@ORA11> select /*+opt_param('star_transformation_enabled','true')
2 star_transformation*/
3 quantity_sold,amount_sold
4 from times t join sales s on t.time_id = s.time_id
5 join products p1 on s.prod_id = p1.prod_id
6 join customers c1 on s.cust_id = c1.cust_id
7 join channels c2 on s.channel_id = c2.channel_id
8 join promotions p2 on s.promo_id = p2.promo_id
9 where c1.cust_first_name = 'Ramsay'
10 and c1.cust_last_name = 'Alden'
11 and t.day_name = 'Saturday'
12 and c2.channel_class = 'Direct'
13 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'
14 and p2.promo_name = 'NO PROMOTION #'
15 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1748952924
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 877 (1)| 00:00:11 | | |
|* 1 | HASH JOIN | | 1 | 46 | 450 (1)| 00:00:06 | | |
| 2 | PARTITION RANGE SUBQUERY | | | 5 | 431 (1)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | | 5 | 431 (1)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 5 | BITMAP AND | | | | | | | |
| 6 | BITMAP MERGE | | | | | | | |
| 7 | BITMAP KEY ITERATION | | | | | | | |
| 8 | BUFFER SORT | | | | | | | |
|* 9 | TABLE ACCESS FULL | CUSTOMERS | 1 | 20 | 405 (1)| 00:00:05 | | |
|* 10 | BITMAP INDEX RANGE SCAN | SALES_CUST_BI | | | | |KEY(SQ)|KEY(SQ)|
| 11 | BITMAP MERGE | | | | | | | |
| 12 | BITMAP KEY ITERATION | | | | | | | |
| 13 | BUFFER SORT | | | | | | | |
|* 14 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 3 (0)| 00:00:01 | | |
|* 15 | BITMAP INDEX RANGE SCAN | SALES_PROD_BI | | | | |KEY(SQ)|KEY(SQ)|
| 16 | BITMAP MERGE | | | | | | | |
| 17 | BITMAP KEY ITERATION | | | | | | | |
| 18 | BUFFER SORT | | | | | | | |
|* 19 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | 3 (0)| 00:00:01 | | |
|* 20 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BI | | | | |KEY(SQ)|KEY(SQ)|
| 21 | BITMAP MERGE | | | | | | | |
| 22 | BITMAP KEY ITERATION | | | | | | | |
| 23 | BUFFER SORT | | | | | | | |
|* 24 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29 | 17 (0)| 00:00:01 | | |
|* 25 | BITMAP INDEX RANGE SCAN | SALES_PROMO_BI | | | | |KEY(SQ)|KEY(SQ)|
|* 26 | TABLE ACCESS FULL | TIMES | 261 | 4437 | 18 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."TIME_ID"="S"."TIME_ID")
9 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')
10 - access("S"."CUST_ID"="C1"."CUST_ID")
14 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')
15 - access("S"."PROD_ID"="P1"."PROD_ID")
19 - filter("C2"."CHANNEL_CLASS"='Direct')
20 - access("S"."CHANNEL_ID"="C2"."CHANNEL_ID")
24 - filter("P2"."PROMO_NAME"='NO PROMOTION #')
25 - access("S"."PROMO_ID"="P2"."PROMO_ID")
26 - filter("T"."DAY_NAME"='Saturday')
Note
-----
- star transformation used for this statement
sh@ORA11> set autotrace off
这里有一个问题要注意一下，星型转化并不比对事实表添加组合索引的方式得到的性能更好。
然而，在实际当中，我们无法为所有可能的查询创建足够的组合索引。但是，通过星型转化，我们只需要在每个外键上创建一个位图索引以支持WHERE子句中可能存在的组合。
当然，我们对于上述的SQL可以进行一步优化，比较为客户名、产品名称创建索引等。下面，我们来看一个相对较简单的SQL。
sh@ORA11> set autotrace traceonly explain
sh@ORA11> select /*+opt_param('star_transformation_enabled','true')
2 star_transformation*/
3 quantity_sold,amount_sold
4 from sales s join products p1 on s.prod_id = p1.prod_id
5 join customers c1 on s.cust_id = c1.cust_id
6 where c1.cust_first_name = 'Ramsay'
7 and c1.cust_last_name = 'Alden'
8 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'
9 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2220034904
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 |12 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 2 | 39 | 7 (0)| 00:00:01 | 1 | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 2 | 39 | 7 (0)| 00:00:01 | 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 4 | BITMAP AND | | | | | | | |
| 5 | BITMAP MERGE | | | | | | | |
| 6 | BITMAP KEY ITERATION | | | | | | | |
| 7 | BUFFER SORT | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 10 | 200 | 3 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | CUST_NAME_IDX | 10 | | 1 (0)| 00:00:01 | | |
|* 10 | BITMAP INDEX RANGE SCAN | SALES_CUST_BI | | | | | 1 | 28 |
| 11 | BITMAP MERGE | | | | | | | |
| 12 | BITMAP KEY ITERATION | | | | | | | |
| 13 | BUFFER SORT | | | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 30 | 2 (0)| 00:00:01 | | |
|* 15 | INDEX RANGE SCAN | PROD_NAME_IDX | 1 | | 1 (0)| 00:00:01 | | |
|* 16 | BITMAP INDEX RANGE SCAN | SALES_PROD_BI | | | | | 1 | 28 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')
10 - access("S"."CUST_ID"="C1"."CUST_ID")
15 - access("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')
16 - access("S"."PROD_ID"="P1"."PROD_ID")
Note
-----
- star transformation used for this statement
sh@ORA11> set autotrace off
上述的索引，我们创建的传统的B*树索引。对于星型转化，我们可以位图连接索引来进一步的优化，如下。
sh@ORA11> create bitmap index sales_prod_bjix on sales(p.prod_name)
2 from sales s,products p
3 where s.prod_id = p.prod_id
4 local
5 /
Index created.
sh@ORA11> create bitmap index sales_cust_bjix on sales(c.cust_first_name,c.cust_last_name)
2 from sales s,customers c
3 where s.cust_id = c.cust_id
4 local
5 /
Index created.
sh@ORA11>
在上述，我们创建了两个位图连接索引，下面，我们来看查询。
sh@ORA11> set autotrace traceonly explain
sh@ORA11> select quantity_sold,amount_sold
2 from sales s join products p on s.prod_id = p.prod_id
3 join customers c on s.cust_id = c.cust_id
4 where c.cust_first_name = 'Ramsay'
5 and c.cust_last_name = 'Alden'
6 and p.prod_name = '17" LCD w/built-in HDTV Tuner'
7 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3815036790
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 386 | 6562 | 59 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 386 | 6562 | 59 (0)| 00:00:01 | 1 | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 386 | 6562 | 59 (0)| 00:00:01 | 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 4 | BITMAP AND | | | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BJIX | | | | | 1 | 28 |
|* 6 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BJIX | | | | | 1 | 28 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("S"."SYS_NC00009$"='Ramsay' AND "S"."SYS_NC00010$"='Alden')
6 - access("S"."SYS_NC00008$"='17" LCD w/built-in HDTV Tuner')
sh@ORA11> set autotrace off
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle优化之星形查询star transformation query教程
本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/1431.html
