咨询热线 ：
028-86922220
×
01
关于我们

公司简介

发展历程

02
服务项目

高端网站建设

微信开发

APP开发

网络营销服务

电商网站定制

生物医药网站建设

外贸网站建设

教育培训网站建设

03
小程序开发

小程序开发

功能应用

客户案例

04
案例展示

网站建设案例

小程序案例

电商平台案例

APP案例

系统平台案例

05
建站动态

网站建设

网站设计

网站营销

小程序

06
联系我们

公司地址

人才招聘

地址：成都市太升南路288号锦天国际A幢1002号

电话：028-86922220

028-86922220

大橙子网站建设，新征程启航

为企业提供网站建设、域名注册、服务器等服务

Oracle12CR2查询转换教程之临时表转换详解

前言

成都创新互联公司坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站制作、成都网站建设、外贸营销网站建设、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的锦州网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！

大家都知道在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 / 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$5E9A798F

Outline 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

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对创新互联的支持。


分享题目：Oracle12CR2查询转换教程之临时表转换详解
URL网址：http://dzwzjz.com/article/jpdops.html

其他资讯

分享至：
关于我们
公司简介 发展历程
服务项目
高端网站建设 小程序开发 APP开发 网络营销
建站知识
行业新闻 建站学堂 常见问题
联系我们
公司地址 人才招聘
成都 达州
电话：028-86922220
地址：成都市太升南路288号锦天国际A幢1002号
电话：028-86922220
地址：达州市南岸区弹子石腾龙大道58号2栋21-6
友情链接： 网站建设报价方案   乐山服务器托管   和县翔豪网站   APP定制   马边网站建设   广安网站建设   重庆电信五里店机房   橡塑保温板   宣威网站建设   南部网站建设   
版权所有：青羊区大橙子信息咨询工作室 蜀ICP备2022028542号-14
在线咨询
服务热线
服务热线：028-86922220
TOP