前言
大家都知道在12cr2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:
sql> show parameter star_transformation_enabled
star_transformation_enabled string false
sql> alter session set star_transformation_enabled='true';
session altered.
sql> select c.cust_city,
2 t.calendar_quarter_desc,
3 sum(s.amount_sold) sales_amount
4 from sales s,
5 times t,
6 customers c,
7 channels ch
8 where s.time_id = t.time_id
9 and s.cust_id = c.cust_id
10 and s.channel_id = ch.channel_id
11 and c.cust_state_province = 'ca'
12 and ch.channel_desc = 'internet'
13 and t.calendar_quarter_desc in ('1999-01','1999-02')
14 group by c.cust_city, t.calendar_quarter_desc;
montara 1999-02 1618.01
pala 1999-01 3263.93
cloverdale 1999-01 52.64
cloverdale 1999-02 266.28
san francisco 1999-01 3058.27
san mateo 1999-01 8754.59
los angeles 1999-01 1886.19
san mateo 1999-02 21399.42
pala 1999-02 936.62
el sobrante 1999-02 3744.03
el sobrante 1999-01 5392.34
quartzhill 1999-01 987.3
legrand 1999-01 26.32
pescadero 1999-01 26.32
arbuckle 1999-02 241.2
quartzhill 1999-02 412.83
montara 1999-01 289.07
arbuckle 1999-01 270.08
san francisco 1999-02 11257
los angeles 1999-02 2128.59
pescadero 1999-02 298.44
legrand 1999-02 18.66
22 rows selected.
优化器使用临时表sys_temp_0fd9d6893_63d6f82来代替customers表，并且使用临时表中的相关列来替换所引用的列cust_id和cust_city。数据库创建带有两列(c0 number,c1 varchar2(30))的临时表(从执行计划中的 6 – (rowset=256) “c0″[number,22], “c1″[varchar2,30]也可以看到)。这些列关联到customers表中的cust_id和cust_city列。
在下面的执行计划中的1,2,3行物化customers子查询到临时表中，在第6行，数据库扫描临时表(代替子查询)来从事实表中构建位图。第27行扫描临时表执行连接返回代替扫描customers表。数据库不用对临时表应用customer表上的过滤条件，因为在物化临时表时已经应用了过滤条件。
sql> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
sql_id a069wzk60bbqd, child number 2
-------------------------------------
select 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 =
'internet' and t.calendar_quarter_desc in ('1999-01','1999-02') group
by c.cust_city, t.calendar_quarter_desc
plan hash value: 2164696140
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | operation | name | starts | e-rows |e-bytes| cost (%cpu)| e-time | pstart| pstop | a-rows | a-time | buffers | reads | writes | omem | 1mem | used-mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | | | 1177 (100)| | | | 22 |00:00:00.25 | 9080 | 86 | 10 | | | |
| 1 | temp table transformation | | 1 | | | | | | | 22 |00:00:00.25 | 9080 | 86 | 10 | | | |
| 2 | load as select | sys_temp_0fd9d6893_63d6f82 | 1 | | | | | | | 0 |00:00:00.04 | 1535 | 0 | 10 | 1042k| 1042k| |
|* 3 | table access full | customers | 1 | 3341 | 86866 | 423 (1)| 00:00:01 | | | 3341 |00:00:00.01 | 1522 | 0 | 0 | | | |
| 4 | hash group by | | 1 | 877 | 49989 | 754 (1)| 00:00:01 | | | 22 |00:00:00.20 | 7538 | 85 | 0 | 1022k| 1022k| 1349k (0)|
|* 5 | hash join | | 1 | 14534 | 809k| 753 (1)| 00:00:01 | | | 964 |00:00:00.20 | 7538 | 85 | 0 | 1572k| 1572k| 1696k (0)|
| 6 | table access full | sys_temp_0fd9d6893_63d6f82 | 1 | 3341 | 50115 | 4 (0)| 00:00:01 | | | 3341 |00:00:00.01 | 18 | 10 | 0 | | | |
|* 7 | hash join | | 1 | 14534 | 596k| 749 (1)| 00:00:01 | | | 964 |00:00:00.19 | 7520 | 75 | 0 | 1538k| 1538k| 1685k (0)|
|* 8 | table access full | times | 1 | 181 | 2896 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | 0 | 0 | | | |
| 9 | view | vw_st_a3f94988 | 1 | 14534 | 369k| 731 (1)| 00:00:01 | | | 964 |00:00:00.18 | 7455 | 75 | 0 | | | |
| 10 | nested loops | | 1 | 14534 | 809k| 706 (1)| 00:00:01 | | | 964 |00:00:00.18 | 7455 | 75 | 0 | | | |
| 11 | partition range subquery | | 1 | 14534 | 397k| 353 (0)| 00:00:01 |key(sq)|key(sq)| 964 |00:00:00.17 | 7271 | 75 | 0 | | | |
| 12 | bitmap conversion to rowids| | 2 | 14534 | 397k| 353 (0)| 00:00:01 | | | 964 |00:00:00.16 | 7204 | 75 | 0 | | | |
| 13 | bitmap and | | 2 | | | | | | | 2 |00:00:00.16 | 7204 | 75 | 0 | | | |
| 14 | bitmap merge | | 2 | | | | | | | 2 |00:00:00.02 | 15 | 5 | 0 | 1024k| 512k| 4096 (0)|
| 15 | bitmap key iteration | | 2 | | | | | | | 2 |00:00:00.02 | 15 | 5 | 0 | | | |
| 16 | buffer sort | | 2 | | | | | | | 2 |00:00:00.01 | 9 | 0 | 0 | 73728 | 73728 | |
|* 17 | table access full | channels | 1 | 1 | 13 | 3 (0)| 00:00:01 | | | 1 |00:00:00.01 | 9 | 0 | 0 | | | |
|* 18 | bitmap index range scan| sales_channel_bix | 2 | | | | |key(sq)|key(sq)| 2 |00:00:00.02 | 6 | 5 | 0 | | | |
| 19 | bitmap merge | | 2 | | | | | | | 2 |00:00:00.02 | 445 | 9 | 0 | 1024k| 512k|39936 (0)|
| 20 | bitmap key iteration | | 2 | | | | | | | 181 |00:00:00.02 | 445 | 9 | 0 | | | |
| 21 | buffer sort | | 2 | | | | | | | 362 |00:00:00.01 | 65 | 0 | 0 | 73728 | 73728 | |
|* 22 | table access full | times | 1 | 181 | 2896 | 18 (0)| 00:00:01 | | | 181 |00:00:00.01 | 65 | 0 | 0 | | | |
|* 23 | bitmap index range scan| sales_time_bix | 362 | | | | |key(sq)|key(sq)| 181 |00:00:00.02 | 380 | 9 | 0 | | | |
| 24 | bitmap merge | | 2 | | | | | | | 2 |00:00:00.13 | 6744 | 61 | 0 | 1024k| 512k|45056 (0)|
| 25 | bitmap key iteration | | 2 | | | | | | | 403 |00:00:00.12 | 6744 | 61 | 0 | | | |
| 26 | buffer sort | | 2 | | | | | | | 6682 |00:00:00.01 | 18 | 0 | 0 | 5512k| 964k| 174k (0)|
| 27 | table access full | sys_temp_0fd9d6893_63d6f82 | 1 | 3341 | 16705 | 4 (0)| 00:00:01 | | | 3341 |00:00:00.01 | 18 | 0 | 0 | | | |
|* 28 | bitmap index range scan| sales_cust_bix | 6682 | | | | |key(sq)|key(sq)| 403 |00:00:00.10 | 6726 | 61 | 0 | | | |
| 29 | table access by user rowid | sales | 964 | 1 | 29 | 378 (0)| 00:00:01 | rowid | rowid | 964 |00:00:00.01 | 184 | 0 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query block name / object alias (identified by operation id):
-------------------------------------------------------------
1 - sel$d5ef7599
2 - sel$f6045c7b
3 - sel$f6045c7b / c@sel$f6045c7b
6 - sel$d5ef7599 / t1@sel$9c741beb
8 - sel$d5ef7599 / t@sel$1
9 - sel$5e9a798f / vw_st_a3f94988@sel$d5ef7599
10 - sel$5e9a798f
12 - sel$5e9a798f / s@sel$1
17 - sel$6ee793b7 / ch@sel$6ee793b7
22 - sel$acf30367 / t@sel$acf30367
27 - sel$e1f9c76c / t1@sel$e1f9c76c
29 - sel$5e9a798f / sys_cp_s@sel$5e9a798f
outline data
-------------
/*+
begin_outline_data
ignore_optim_embedded_hints
optimizer_features_enable('12.2.0.1')
db_version('12.2.0.1')
opt_param('star_transformation_enabled' 'true')
all_rows
no_parallel
outline_leaf(@"sel$f6045c7b")
outline_leaf(@"sel$acf30367")
outline_leaf(@"sel$6ee793b7")
outline_leaf(@"sel$e1f9c76c")
outline_leaf(@"sel$5e9a798f")
table_lookup_by_nl(@"sel$0e028fd0" "s"@"sel$1")
outline_leaf(@"sel$d5ef7599")
outline(@"sel$1")
outline(@"sel$0e028fd0")
outline(@"sel$c3af6d21")
eliminate_join(@"sel$1" "ch"@"sel$1")
outline(@"sel$5208623c")
star_transformation(@"sel$1" "s"@"sel$1" subqueries(("t"@"sel$1") ("ch"@"sel$1") temp_table("c"@"sel$1")))
full(@"sel$d5ef7599" "t"@"sel$1")
no_access(@"sel$d5ef7599" "vw_st_a3f94988"@"sel$d5ef7599")
full(@"sel$d5ef7599" "t1"@"sel$9c741beb")
leading(@"sel$d5ef7599" "t"@"sel$1" "vw_st_a3f94988"@"sel$d5ef7599" "t1"@"sel$9c741beb")
use_hash(@"sel$d5ef7599" "vw_st_a3f94988"@"sel$d5ef7599")
use_hash(@"sel$d5ef7599" "t1"@"sel$9c741beb")
swap_join_inputs(@"sel$d5ef7599" "t1"@"sel$9c741beb")
use_hash_aggregation(@"sel$d5ef7599")
bitmap_and(@"sel$5e9a798f" "s"@"sel$1" ("sales"."channel_id") 1)
bitmap_and(@"sel$5e9a798f" "s"@"sel$1" ("sales"."time_id") 2)
bitmap_and(@"sel$5e9a798f" "s"@"sel$1" ("sales"."cust_id") 3)
rowid(@"sel$5e9a798f" "sys_cp_s"@"sel$5e9a798f")
leading(@"sel$5e9a798f" "s"@"sel$1" "sys_cp_s"@"sel$5e9a798f")
subquery_pruning(@"sel$5e9a798f" "s"@"sel$1" partition)
use_nl(@"sel$5e9a798f" "sys_cp_s"@"sel$5e9a798f")
full(@"sel$e1f9c76c" "t1"@"sel$e1f9c76c")
semijoin_driver(@"sel$e1f9c76c")
full(@"sel$6ee793b7" "ch"@"sel$6ee793b7")
semijoin_driver(@"sel$6ee793b7")
full(@"sel$acf30367" "t"@"sel$acf30367")
semijoin_driver(@"sel$acf30367")
full(@"sel$f6045c7b" "c"@"sel$f6045c7b")
semijoin_driver(@"sel$f6045c7b")
end_outline_data
*/
predicate information (identified by operation id):
---------------------------------------------------
3 - filter("c"."cust_state_province"='ca')
5 - access("item_1"="c0")
7 - access("item_2"="t"."time_id")
8 - filter(("t"."calendar_quarter_desc"='1999-01' or "t"."calendar_quarter_desc"='1999-02'))
17 - filter("ch"."channel_desc"='internet')
18 - access("s"."channel_id"="ch"."channel_id")
22 - filter(("t"."calendar_quarter_desc"='1999-01' or "t"."calendar_quarter_desc"='1999-02'))
23 - access("s"."time_id"="t"."time_id")
28 - access("s"."cust_id"="c0")
column projection information (identified by operation id):
-----------------------------------------------------------
1 - "c1"[varchar2,30], "t"."calendar_quarter_desc"[character,7], sum("item_3")[22]
2 - sysdef[4], sysdef[0], sysdef[1], sysdef[120], sysdef[0]
3 - "c"."cust_id"[number,22], "c"."cust_city"[varchar2,30], "c"."cust_state_province"[varchar2,40]
4 - "c1"[varchar2,30], "t"."calendar_quarter_desc"[character,7], sum("item_3")[22]
5 - (#keys=1; rowset=256) "c0"[number,22], "item_1"[number,22], "c1"[varchar2,30], "t"."time_id"[date,7], "item_2"[date,7], "t"."calendar_quarter_desc"[character,7], "item_3"[number,22]
6 - (rowset=256) "c0"[number,22], "c1"[varchar2,30]
7 - (#keys=1; rowset=256) "t"."time_id"[date,7], "item_2"[date,7], "t"."calendar_quarter_desc"[character,7], "item_1"[number,22], "item_3"[number,22]
8 - (rowset=256) "t"."time_id"[date,7], "t"."calendar_quarter_desc"[character,7]
9 - "item_1"[number,22], "item_2"[date,7], "item_3"[number,22]
10 - rowid[rowid,10], rowid[rowid,10], "s"."cust_id"[number,22], "s"."time_id"[date,7], "s"."amount_sold"[number,22]
11 - rowid[rowid,10]
12 - rowid[rowid,10]
13 - strdef[bm var, 10], strdef[bm var, 10], strdef[bm var, 32496]
14 - strdef[bm var, 10], strdef[bm var, 10], strdef[bm var, 32496]
15 - strdef[10], strdef[10], strdef[7920], "s"."channel_id"[number,22]
16 - (#keys=2) "ch"."channel_id"[number,22], "ch"."channel_desc"[varchar2,20]
17 - (rowset=256) "ch"."channel_id"[number,22], "ch"."channel_desc"[varchar2,20]
18 - strdef[bm var, 10], strdef[bm var, 10], strdef[bm var, 7920], "s"."channel_id"[number,22]
19 - strdef[bm var, 10], strdef[bm var, 10], strdef[bm var, 32496]
20 - strdef[10], strdef[10], strdef[7920], "s"."time_id"[date,7]
21 - (#keys=2) "t"."time_id"[date,7], "t"."calendar_quarter_desc"[character,7]
22 - (rowset=256) "t"."time_id"[date,7], "t"."calendar_quarter_desc"[character,7]
23 - strdef[bm var, 10], strdef[bm var, 10], strdef[bm var, 7920], "s"."time_id"[date,7]
24 - strdef[bm var, 10], strdef[bm var, 10], strdef[bm var, 32496]
25 - strdef[10], strdef[10], strdef[7920], "s"."cust_id"[number,22]
26 - (#keys=1) "c0"[number,22]
27 - (rowset=256) "c0"[number,22]
28 - strdef[bm var, 10], strdef[bm var, 10], strdef[bm var, 7920], "s"."cust_id"[number,22]
29 - rowid[rowid,10], "s"."cust_id"[number,22], "s"."time_id"[date,7], "s"."amount_sold"[number,22]
note
-----
- automatic dop: computed degree of parallelism is 1 because of parallel threshold
- cbqt star transformation used for this statement
- this is an adaptive plan
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对www.887551.com的支持。