前言
大家都知道在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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表上的过滤条件，因为在物化临时表时已经应用了过滤条件。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 |
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 |0.25 | 9080 | 86 | 10 | | | | | 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | | | 22 |0.25 | 9080 | 86 | 10 | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6893_63D6F82 | 1 | | | | | | | 0 |0.04 | 1535 | 0 | 10 | 1042K| 1042K| | |* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 3341 | 86866 | 423 (1)| 00:00:01 | | | 3341 |0.01 | 1522 | 0 | 0 | | | | | 4 | HASH GROUP BY | | 1 | 877 | 49989 | 754 (1)| 00:00:01 | | | 22 |0.20 | 7538 | 85 | 0 | 1022K| 1022K| 1349K (0)| |* 5 | HASH JOIN | | 1 | 14534 | 809K| 753 (1)| 00:00:01 | | | 964 |0.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 |0.01 | 18 | 10 | 0 | | | | |* 7 | HASH JOIN | | 1 | 14534 | 596K| 749 (1)| 00:00:01 | | | 964 |0.19 | 7520 | 75 | 0 | 1538K| 1538K| 1685K (0)| |* 8 | TABLE ACCESS FULL | TIMES | 1 | 181 | 2896 | 18 (0)| 00:00:01 | | | 181 |0.01 | 65 | 0 | 0 | | | | | 9 | VIEW | VW_ST_A3F94988 | 1 | 14534 | 369K| 731 (1)| 00:00:01 | | | 964 |0.18 | 7455 | 75 | 0 | | | | | 10 | NESTED LOOPS | | 1 | 14534 | 809K| 706 (1)| 00:00:01 | | | 964 |0.18 | 7455 | 75 | 0 | | | | | 11 | PARTITION RANGE SUBQUERY | | 1 | 14534 | 397K| 353 (0)| 00:00:01 | KEY (SQ)| KEY (SQ)| 964 |0.17 | 7271 | 75 | 0 | | | | | 12 | BITMAP CONVERSION TO ROWIDS| | 2 | 14534 | 397K| 353 (0)| 00:00:01 | | | 964 |0.16 | 7204 | 75 | 0 | | | | | 13 | BITMAP AND | | 2 | | | | | | | 2 |0.16 | 7204 | 75 | 0 | | | | | 14 | BITMAP MERGE | | 2 | | | | | | | 2 |0.02 | 15 | 5 | 0 | 1024K| 512K| 4096 (0)| | 15 | BITMAP KEY ITERATION | | 2 | | | | | | | 2 |0.02 | 15 | 5 | 0 | | | | | 16 | BUFFER SORT | | 2 | | | | | | | 2 |0.01 | 9 | 0 | 0 | 73728 | 73728 | | |* 17 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 13 | 3 (0)| 00:00:01 | | | 1 |0.01 | 9 | 0 | 0 | | | | |* 18 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | 2 | | | | | KEY (SQ)| KEY (SQ)| 2 |0.02 | 6 | 5 | 0 | | | | | 19 | BITMAP MERGE | | 2 | | | | | | | 2 |0.02 | 445 | 9 | 0 | 1024K| 512K|39936 (0)| | 20 | BITMAP KEY ITERATION | | 2 | | | | | | | 181 |0.02 | 445 | 9 | 0 | | | | | 21 | BUFFER SORT | | 2 | | | | | | | 362 |0.01 | 65 | 0 | 0 | 73728 | 73728 | | |* 22 | TABLE ACCESS FULL | TIMES | 1 | 181 | 2896 | 18 (0)| 00:00:01 | | | 181 |0.01 | 65 | 0 | 0 | | | | |* 23 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | 362 | | | | | KEY (SQ)| KEY (SQ)| 181 |0.02 | 380 | 9 | 0 | | | | | 24 | BITMAP MERGE | | 2 | | | | | | | 2 |0.13 | 6744 | 61 | 0 | 1024K| 512K|45056 (0)| | 25 | BITMAP KEY ITERATION | | 2 | | | | | | | 403 |0.12 | 6744 | 61 | 0 | | | | | 26 | BUFFER SORT | | 2 | | | | | | | 6682 |0.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 |0.01 | 18 | 0 | 0 | | | | |* 28 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | 6682 | | | | | KEY (SQ)| KEY (SQ)| 403 |0.10 | 6726 | 61 | 0 | | | | | 29 | TABLE ACCESS BY USER ROWID | SALES | 964 | 1 | 29 | 378 (0)| 00:00:01 | ROWID | ROWID | 964 |0.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 |
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对服务器之家的支持。
原文链接：http://www.jydba.net/oracle-12cr2查询转换之临时转换/
查看更多关于Oracle 12CR2查询转换教程之临时表转换详解的详细内容...