Oracle优化——星型联结转换（star_transformation_enabled）
程序员文章站
2022-03-09 07:52:44
另外请了解位图联结索引，此类问题的最佳实践。
这是一个星型联结的例子，sales为事实表，其它为维度表。where条件全部都作用在维度表上面。
sh@ prod> select quan...
另外请了解位图联结索引，此类问题的最佳实践。
这是一个星型联结的例子，sales为事实表，其它为维度表。where条件全部都作用在维度表上面。
sh@ prod> select quantity_sold , amount_sold from sales s join products p using ( prod_id )
2 join times using ( time_id ) join customers c using ( cust_id )
3 where week_ending_day = '29-nov-2008'
4 and prod_name = '1.44mb external 3.5'' diskette'
5 and cust_year_of_birth = 1965 ;
no rows selected
execution plan
----------------------------------------------------------
plan hash value: 3891315047
--------------------------------------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 80 | 30 (0)| 00:00:01 | | |
| 1 | nested loops | | | | | | | |
| 2 | nested loops | | 1 | 80 | 30 (0)| 00:00:01 | | |
| 3 | nested loops | | 4 | 284 | 26 (0)| 00:00:01 | | |
| 4 | merge join cartesian | | 1 | 46 | 21 (0)| 00:00:01 | | |
|* 5 | table access full | times | 1 | 16 | 18 (0)| 00:00:01 | | |
| 6 | buffer sort | | 1 | 30 | 3 (0)| 00:00:01 | | |
|* 7 | table access full | products | 1 | 30 | 3 (0)| 00:00:01 | | |
| 8 | table access by global index rowid| sales | 9 | 225 | 5 (0)| 00:00:01 | rowid | rowid |
|* 9 | index range scan | sales_concat_idx | 9 | | 2 (0)| 00:00:01 | | |
|* 10 | index unique scan | customers_pk | 1 | | 0 (0)| 00:00:01 | | |
|* 11 | table access by index rowid | customers | 1 | 9 | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
5 - filter("times"."week_ending_day"=to_date(' 2008-11-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - filter("p"."prod_name"='1.44mb external 3.5'' diskette')
9 - access("s"."prod_id"="p"."prod_id" and "s"."time_id"="times"."time_id")
10 - access("s"."cust_id"="c"."cust_id")
11 - filter("c"."cust_year_of_birth"=1965)
statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
414 bytes sent via sql*net to client
512 bytes received via sql*net from client
1 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0rows processed
执行计划中用到了笛卡尔联结。
其实上面的查询等效于下面的（连执行计划都是一样的）：
sh@ prod> select quantity_sold , amount_sold from sales s
2 where s.prod_id in ( select prod_id from products where prod_name = '1.44mb external 3.5" diskette' )
3 and s.time_id in ( select time_id from times where week_ending_day = '29-nov-2008' )
4 and s.cust_id in ( select cust_id from customers where cust_year_of_birth = 1965 ) ;
no rows selected
execution plan
----------------------------------------------------------
plan hash value: 3891315047
--------------------------------------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 80 | 30 (0)| 00:00:01 | | |
| 1 | nested loops | | | | | | | |
| 2 | nested loops | | 1 | 80 | 30 (0)| 00:00:01 | | |
| 3 | nested loops | | 4 | 284 | 26 (0)| 00:00:01 | | |
| 4 | merge join cartesian | | 1 | 46 | 21 (0)| 00:00:01 | | |
|* 5 | table access full | times | 1 | 16 | 18 (0)| 00:00:01 | | |
| 6 | buffer sort | | 1 | 30 | 3 (0)| 00:00:01 | | |
|* 7 | table access full | products | 1 | 30 | 3 (0)| 00:00:01 | | |
| 8 | table access by global index rowid| sales | 9 | 225 | 5 (0)| 00:00:01 | rowid | rowid |
|* 9 | index range scan | sales_concat_idx | 9 | | 2 (0)| 00:00:01 | | |
|* 10 | index unique scan | customers_pk | 1 | | 0 (0)| 00:00:01 | | |
|* 11 | table access by index rowid | customers | 1 | 9 | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
5 - filter("week_ending_day"=to_date(' 2008-11-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - filter("prod_name"='1.44mb external 3.5" diskette')
9 - access("s"."prod_id"="prod_id" and "s"."time_id"="time_id")
10 - access("s"."cust_id"="cust_id")
11 - filter("cust_year_of_birth"=1965)
statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
414 bytes sent via sql*net to client
512 bytes received via sql*net from client
1 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
使能星型联结转换（注意，星型联结转换默认不是使能的）
sh@ prod> alter session set star_transformation_enabled = true ;
session altered.
再看两个表的执行计划，其实还是一样的，只不过更多的使用了位图合并。这需要在事实表的每个外键上添加位图索引。
这种叫做星型转换，一般用在数据仓库中。
sh@ prod> select quantity_sold , amount_sold from sales s
2 where s.prod_id in ( select prod_id from products where prod_name = '1.44mb external 3.5" diskette' )
3 and s.time_id in ( select time_id from times where week_ending_day = '29-nov-2008' )
4 and s.cust_id in ( select cust_id from customers where cust_year_of_birth = 1965 ) ;
no rows selected
execution plan
----------------------------------------------------------
plan hash value: 3061144765
-----------------------------------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 34 | 53 (0)| 00:00:01 | | |
| 1 | nested loops | | | | | | | |
| 2 | nested loops | | 1 | 34 | 32 (0)| 00:00:01 | | |
| 3 | partition range subquery | | 8 | 218 | 23 (0)| 00:00:01 |key(sq)|key(sq)|
| 4 | table access by local index rowid| sales | 8 | 218 | 23 (0)| 00:00:01 |key(sq)|key(sq)|
| 5 | bitmap conversion to rowids | | | | | | | |
| 6 | bitmap and | | | | | | | |
| 7 | bitmap merge | | | | | | | |
| 8 | bitmap key iteration | | | | | | | |
| 9 | buffer sort | | | | | | | |
|* 10 | table access full | times | 1 | 16 | 18 (0)| 00:00:01 | | |
|* 11 | bitmap index range scan | sales_time_bix | | | | |key(sq)|key(sq)|
| 12 | bitmap merge | | | | | | | |
| 13 | bitmap key iteration | | | | | | | |
| 14 | buffer sort | | | | | | | |
|* 15 | table access full | products | 1 | 30 | 3 (0)| 00:00:01 | | |
|* 16 | bitmap index range scan | sales_prod_bix | | | | |key(sq)|key(sq)|
|* 17 | index unique scan | customers_pk | 1 | | 0 (0)| 00:00:01 | | |
|* 18 | table access by index rowid | customers | 1 | 9 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
10 - filter("week_ending_day"=to_date(' 2008-11-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
11 - access("s"."time_id"="time_id")
15 - filter("prod_name"='1.44mb external 3.5" diskette')
16 - access("s"."prod_id"="prod_id")
17 - access("s"."cust_id"="cust_id")
18 - filter("cust_year_of_birth"=1965)
note
-----
- star transformation used for this statement
statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
414 bytes sent via sql*net to client
512 bytes received via sql*net from client
1 sql*net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
sh@ prod> select quantity_sold , amount_sold from sales s join products p using ( prod_id )
2 join times using ( time_id ) join customers c using ( cust_id )
3 where week_ending_day = '29-nov-2008'
4 and prod_name = '1.44mb external 3.5'' diskette'
5 and cust_year_of_birth = 1965 ;
no rows selected
execution plan
----------------------------------------------------------
plan hash value: 3061144765
-----------------------------------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 34 | 53 (0)| 00:00:01 | | |
| 1 | nested loops | | | | | | | |
| 2 | nested loops | | 1 | 34 | 32 (0)| 00:00:01 | | |
| 3 | partition range subquery | | 8 | 218 | 23 (0)| 00:00:01 |key(sq)|key(sq)|
| 4 | table access by local index rowid| sales | 8 | 218 | 23 (0)| 00:00:01 |key(sq)|key(sq)|
| 5 | bitmap conversion to rowids | | | | | | | |
| 6 | bitmap and | | | | | | | |
| 7 | bitmap merge | | | | | | | |
| 8 | bitmap key iteration | | | | | | | |
| 9 | buffer sort | | | | | | | |
|* 10 | table access full | times | 1 | 16 | 18 (0)| 00:00:01 | | |
|* 11 | bitmap index range scan | sales_time_bix | | | | |key(sq)|key(sq)|
| 12 | bitmap merge | | | | | | | |
| 13 | bitmap key iteration | | | | | | | |
| 14 | buffer sort | | | | | | | |
|* 15 | table access full | products | 1 | 30 | 3 (0)| 00:00:01 | | |
|* 16 | bitmap index range scan | sales_prod_bix | | | | |key(sq)|key(sq)|
|* 17 | index unique scan | customers_pk | 1 | | 0 (0)| 00:00:01 | | |
|* 18 | table access by index rowid | customers | 1 | 9 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
10 - filter("times"."week_ending_day"=to_date(' 2008-11-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
11 - access("s"."time_id"="times"."time_id")
15 - filter("p"."prod_name"='1.44mb external 3.5'' diskette')
16 - access("s"."prod_id"="p"."prod_id")
17 - access("s"."cust_id"="c"."cust_id")
18 - filter("c"."cust_year_of_birth"=1965)
note
-----
- star transformation used for this statement
statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
414 bytes sent via sql*net to client
512 bytes received via sql*net from client
1 sql*net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed 上一篇： DB2优化（简易版）
下一篇： spring__aop织入