Oracle 12CR2查询转换之临时表转换

2025-04-11 技术教程

在12CR2中出现一种新的查询转换技术临时表转换，在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:

SQL>showparameterstar_transformation_enabledstar_transformation_enabledstringFALSESQL>altersessionsetstar_transformation_enabled='true';Sessionaltered.SQL>SELECTc.cust_city,2t.calendar_quarter_desc,3SUM(s.amount_sold)sales_amount4FROMsaless,5timest,6customersc,7channelsch8WHEREs.time_id=t.time_id9ANDs.cust_id=c.cust_id10ANDs.channel_id=ch.channel_id11ANDc.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.01Pala1999-013263.93Cloverdale1999-0152.64Cloverdale1999-02266.28SanFrancisco1999-013058.27SanMateo1999-018754.59LosAngeles1999-011886.19SanMateo1999-0221399.42Pala1999-02936.62ElSobrante1999-023744.03ElSobrante1999-015392.34Quartzhill1999-01987.3Legrand1999-0126.32Pescadero1999-0126.32Arbuckle1999-02241.2Quartzhill1999-02412.83Montara1999-01289.07Arbuckle1999-01270.08SanFrancisco1999-0211257LosAngeles1999-022128.59Pescadero1999-02298.44Legrand1999-0218.6622rowsselected.

优化器使用临时表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*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,channelschWHEREs.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')GROUPBYc.cust_city,t.calendar_quarter_descPlanhashvalue: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$D5EF75992-SEL$F6045C7B3-SEL$F6045C7B/C@SEL$F6045C7B6-SEL$D5EF7599/T1@SEL$9C741BEB8-SEL$D5EF7599/T@SEL$19-SEL$5E9A798F/VW_ST_A3F94988@SEL$D5EF759910-SEL$5E9A798F12-SEL$5E9A798F/S@SEL$117-SEL$6EE793B7/CH@SEL$6EE793B722-SEL$ACF30367/T@SEL$ACF3036727-SEL$E1F9C76C/T1@SEL$E1F9C76C29-SEL$5E9A798F/SYS_CP_S@SEL$5E9A798FOutlineData-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('12.2.0.1')DB_VERSION('12.2.0.1')OPT_PARAM('star_transformation_enabled''true')ALL_ROWSNO_PARALLELOUTLINE_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