Oracle 12CR2查询转换教程之临时表转换详解
前言
大家都知道在12CR2中出现一种新的查询转换技术临时表转换，在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:
SQL>showparameterstar_transformation_enabled
star_transformation_enabledstringFALSE
SQL>altersessionsetstar_transformation_enabled='true';
Sessionaltered.
SQL>SELECTc.cust_city,
2t.calendar_quarter_desc,
3SUM(s.amount_sold)sales_amount
4FROMsaless,
5timest,
6customersc,
7channelsch
8WHEREs.time_id=t.time_id
9ANDs.cust_id=c.cust_id
10ANDs.channel_id=ch.channel_id
11ANDc.cust_state_province='CA'
12ANDch.channel_desc='Internet'
13ANDt.calendar_quarter_descIN('1999-01','1999-02')
14GROUPBYc.cust_city,t.calendar_quarter_desc;
Montara1999-021618.01
Pala1999-013263.93
Cloverdale1999-0152.64
Cloverdale1999-02266.28
SanFrancisco1999-013058.27
SanMateo1999-018754.59
LosAngeles1999-011886.19
SanMateo1999-0221399.42
Pala1999-02936.62
ElSobrante1999-023744.03
ElSobrante1999-015392.34
Quartzhill1999-01987.3
Legrand1999-0126.32
Pescadero1999-0126.32
Arbuckle1999-02241.2
Quartzhill1999-02412.83
Montara1999-01289.07
Arbuckle1999-01270.08
SanFrancisco1999-0211257
LosAngeles1999-022128.59
Pescadero1999-02298.44
Legrand1999-0218.66
22rowsselected.
优化器使用临时表SYS_TEMP_0FD9D6893_63D6F82来代替customers表，并且使用临时表中的相关列来替换所引用的列cust_id和cust_city。数据库创建带有两列(c0number,c1varchar2(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*fromtable(dbms_xplan.display_cursor(null,null,'advancedallstatslastrunstats_lastpeeked_binds'));
SQL_IDa069wzk60bbqd,childnumber2
-------------------------------------
SELECTc.cust_city,t.calendar_quarter_desc,SUM(s.amount_sold)
sales_amountFROMsaless,timest,customersc,channelschWHERE
s.time_id=t.time_idANDs.cust_id=c.cust_idANDs.channel_id=
ch.channel_idANDc.cust_state_province='CA'ANDch.channel_desc=
'Internet'ANDt.calendar_quarter_descIN('1999-01','1999-02')GROUP
BYc.cust_city,t.calendar_quarter_desc
Planhashvalue: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|SELECTSTATEMENT||1|||1177(100)||||22|00:00:00.25|9080|86|10||||
|1|TEMPTABLETRANSFORMATION||1|||||||22|00:00:00.25|9080|86|10||||
|2|LOADASSELECT|SYS_TEMP_0FD9D6893_63D6F82|1|||||||0|00:00:00.04|1535|0|10|1042K|1042K||
|*3|TABLEACCESSFULL|CUSTOMERS|1|3341|86866|423(1)|00:00:01|||3341|00:00:00.01|1522|0|0||||
|4|HASHGROUPBY||1|877|49989|754(1)|00:00:01|||22|00:00:00.20|7538|85|0|1022K|1022K|1349K(0)|
|*5|HASHJOIN||1|14534|809K|753(1)|00:00:01|||964|00:00:00.20|7538|85|0|1572K|1572K|1696K(0)|
|6|TABLEACCESSFULL|SYS_TEMP_0FD9D6893_63D6F82|1|3341|50115|4(0)|00:00:01|||3341|00:00:00.01|18|10|0||||
|*7|HASHJOIN||1|14534|596K|749(1)|00:00:01|||964|00:00:00.19|7520|75|0|1538K|1538K|1685K(0)|
|*8|TABLEACCESSFULL|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|NESTEDLOOPS||1|14534|809K|706(1)|00:00:01|||964|00:00:00.18|7455|75|0||||
|11|PARTITIONRANGESUBQUERY||1|14534|397K|353(0)|00:00:01|KEY(SQ)|KEY(SQ)|964|00:00:00.17|7271|75|0||||
|12|BITMAPCONVERSIONTOROWIDS||2|14534|397K|353(0)|00:00:01|||964|00:00:00.16|7204|75|0||||
|13|BITMAPAND||2|||||||2|00:00:00.16|7204|75|0||||
|14|BITMAPMERGE||2|||||||2|00:00:00.02|15|5|0|1024K|512K|4096(0)|
|15|BITMAPKEYITERATION||2|||||||2|00:00:00.02|15|5|0||||
|16|BUFFERSORT||2|||||||2|00:00:00.01|9|0|0|73728|73728||
|*17|TABLEACCESSFULL|CHANNELS|1|1|13|3(0)|00:00:01|||1|00:00:00.01|9|0|0||||
|*18|BITMAPINDEXRANGESCAN|SALES_CHANNEL_BIX|2|||||KEY(SQ)|KEY(SQ)|2|00:00:00.02|6|5|0||||
|19|BITMAPMERGE||2|||||||2|00:00:00.02|445|9|0|1024K|512K|39936(0)|
|20|BITMAPKEYITERATION||2|||||||181|00:00:00.02|445|9|0||||
|21|BUFFERSORT||2|||||||362|00:00:00.01|65|0|0|73728|73728||
|*22|TABLEACCESSFULL|TIMES|1|181|2896|18(0)|00:00:01|||181|00:00:00.01|65|0|0||||
|*23|BITMAPINDEXRANGESCAN|SALES_TIME_BIX|362|||||KEY(SQ)|KEY(SQ)|181|00:00:00.02|380|9|0||||
|24|BITMAPMERGE||2|||||||2|00:00:00.13|6744|61|0|1024K|512K|45056(0)|
|25|BITMAPKEYITERATION||2|||||||403|00:00:00.12|6744|61|0||||
|26|BUFFERSORT||2|||||||6682|00:00:00.01|18|0|0|5512K|964K|174K(0)|
|27|TABLEACCESSFULL|SYS_TEMP_0FD9D6893_63D6F82|1|3341|16705|4(0)|00:00:01|||3341|00:00:00.01|18|0|0||||
|*28|BITMAPINDEXRANGESCAN|SALES_CUST_BIX|6682|||||KEY(SQ)|KEY(SQ)|403|00:00:00.10|6726|61|0||||
|29|TABLEACCESSBYUSERROWID|SALES|964|1|29|378(0)|00:00:01|ROWID|ROWID|964|00:00:00.01|184|0|0||||
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QueryBlockName/ObjectAlias(identifiedbyoperationid):
-------------------------------------------------------------
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
OutlineData
-------------
/*+
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
*/
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
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")
ColumnProjectionInformation(identifiedbyoperationid):
-----------------------------------------------------------
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[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,32496]
14-STRDEF[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,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[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,7920],"S"."CHANNEL_ID"[NUMBER,22]
19-STRDEF[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,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[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,7920],"S"."TIME_ID"[DATE,7]
24-STRDEF[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,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[BMVAR,10],STRDEF[BMVAR,10],STRDEF[BMVAR,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
-----
-automaticDOP:ComputedDegreeofParallelismis1becauseofparallelthreshold
-cbqtstartransformationusedforthisstatement
-thisisanadaptiveplan
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。