Oracle 12CR2查询转换教程之临时表转换详解

前言

大家都知道在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

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。