Oracle 12CR2查询转换之临时表转换
发表于：2025-11-17 作者：千家信息网编辑
千家信息网最后更新 2025年11月17日，在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:SQL> show parameter star_transforma
千家信息网最后更新 2025年11月17日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安全错误
数据库的锁怎样保障安全
为什么要举办网络安全宣传
内蒙古通讯软件开发创意
豆客服务器怎么设置
科技互联网与传统媒体的区别
适合个人用的服务器
阿里云服务器代金券合作伙伴
逃脱者2联机断开服务器
塔式服务器diy
通用数据库软件
软件开发定制软件开发
冒险岛进什么服务器
做软件开发用笔记本推荐
厦门吉比特网络技术股权激励
尤奇网网络技术
山东做软件开发
所选源数据库不包含可见表或视图
畜禽养殖环境调控系统软件开发
java内存列式数据库
江湖风云录用什么软件开发的
海康威视软件开发做什么
计算机三级网络技术怎么自学
连不上2ksports服务器怎么办
数据库能接收多少个ado连接
软件开发最好的一本大学
税务部门 网络安全责任书
软件开发定制软件开发
数据库与内存效率
局域网 excel服务器
魔兽世界服务器人少怎么解决
外文数据库开放
