欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-28 20:04:58
前言 大家都知道在12cr2中出现一种新的查询转换技术临时表转换, 在下面的例子中,数据库对customers表上的子查询结果物化到一个临时表中: sql>...

前言

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

总结

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