Oracle 12CR2查询转换教程之临时表转换详解
发表于：2026-11-01 作者：千家信息网编辑
千家信息网最后更新 2026年11月01日，前言大家都知道在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:SQL> show parameter star_tra
千家信息网最后更新 2026年11月01日Oracle 12CR2查询转换教程之临时表转换详解
前言
大家都知道在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:
SQL> show parameter star_transformation_enabledstar_transformation_enabled string FALSESQL> 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.01Pala 1999-01 3263.93Cloverdale 1999-01 52.64Cloverdale 1999-02 266.28San Francisco 1999-01 3058.27San Mateo 1999-01 8754.59Los Angeles 1999-01 1886.19San Mateo 1999-02 21399.42Pala 1999-02 936.62El Sobrante 1999-02 3744.03El Sobrante 1999-01 5392.34Quartzhill 1999-01 987.3Legrand 1999-01 26.32Pescadero 1999-01 26.32Arbuckle 1999-02 241.2Quartzhill 1999-02 412.83Montara 1999-01 289.07Arbuckle 1999-01 270.08San Francisco 1999-02 11257Los Angeles 1999-02 2128.59Pescadero 1999-02 298.44Legrand 1999-02 18.6622 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 WHEREs.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') GROUPBY c.cust_city, t.calendar_quarter_descPlan 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$5E9A798FOutline 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总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。
查询
数据
数据库
内容
条件
面的
学习
应用
不用
事实
价值
位图
例子
前言
就是
技术
疑问
篇文章
结果
交流
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
西安纸贵互联网科技有限公司
深圳互联网科技公司
猫创互联网科技
互联网科技创新企业排名
农村开互联网科技讲堂
须眉科技是互联网公司吗
现代互联网科技股
受益于互联网科技的高速发展
厦门比较好的科技互联网公司
坤平互联网科技
互联网科技如何创业
云南省自由互联网科技有限公司
张召忠谈中国互联网科技
互联网科技数字货币
互联网科技快讯热点
世界互联网科技成果奖
中国新注册互联网科技
互联网科技创新创业项目
优雇互联网科技有限公司
小牛互联网科技有限公司地址
云之行互联网科技
科技与互联网
宜安科技 互联网金融
深圳齐家互联网科技
互联网金融科技委员会
新华互联网科技2019
速维有品互联网信息科技(苏州)有限公司
互联网科技扶贫时间
互联网科技圈年度人物
西安左小互联网科技有限公司
