Oracle 12CR2查询转换之临时表转换
发表于：2025-01-24 作者：千家信息网编辑
千家信息网最后更新 2025年01月24日，在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:SQL> show parameter star_transforma
千家信息网最后更新 2025年01月24日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安全错误
数据库的锁怎样保障安全
软件测试使用数据库案例
木星服务器
蜜罐技术在网络安全中的应用
什么是网络技术发展的动力
网络安全_网银漏洞
仿12306铁路数据库
云sql数据库
网络安全宣传作文500字
云南管理系统软件开发哪家好
sql网络安全教程
中刊网算主流数据库吗
软件开发行业合同履约成本
网络安全知识问答大全
重庆交友软件开发如何收费
第九届首都网络安全宣传日
小储发卡数据库文件
一种是系统数据库
手机雀魂连不上服务器
玉州公共信息网络安全监察科长
电信校招网络技术面试题库
杭州工业软件开发费用是多少
木星服务器
新上市科技互联网公司的股票
后台做好数据库后如何和前台
数据库系统概括第五版视频
腾讯服务器技术
服务器现场交付流程
网络安全知识问答大全
宁晋县网络安全和信息化
不属于网络技术发展的趋势
