服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Oracle - Oracle 12CR2查询转换教程之临时表转换详解

Oracle 12CR2查询转换教程之临时表转换详解

2020-04-03 18:04jydba Oracle

这篇文章主要给大家介绍了关于Oracle 12CR2查询转换教程之临时表转换的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧

前言

大家都知道在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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表上的过滤条件，因为在物化临时表时已经应用了过滤条件。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
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

总结

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

原文链接：http://www.jydba.net/oracle-12cr2查询转换之临时转换/

  • 临时表
  • 查询转换
  • Oracle 12CR2

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    1033 　 1455 　 12154 　 10231 　 12514 　 54013 　 数据库系统 　 快照 　 分区策略 　 数据库集 　 异常关闭 　 Oracle9iPL 　 SQL编程 　 日期函数 　 SQL性能优化 　 并行查询 　 紧急故障 　 完全卸载 　 Win2k 　 IMP 　 分区管理 　 密码文件 　 AutoUpgrade 　 异地备份 　 交叉表 　 加载数据库错误 　 areasQueries 　 逻辑备份 　 SH文件 　 数据文件 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图