前言
大家都知道在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 / 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_ DED_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
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。
继续阅读与本文标签相同的文章
上一篇 :
对numpy中二进制格式的数据存储与读取方法详解
下一篇 :
自然语言处理技术及行业应用案例
-
望闻问切，用好人工智能
2026-05-19栏目： 教程
-
数字时代如何保护个人信息
2026-05-19栏目： 教程
-
SPAR国际物流峰会在威海召开
2026-05-19栏目： 教程
-
NewBalance新款3D打印运动鞋 改变前掌中底
2026-05-19栏目： 教程
-
简易区分物联网和互联网
2026-05-19栏目： 教程