-
Notifications
You must be signed in to change notification settings - Fork 0
/
cbo_View_Merge
295 lines (217 loc) · 14.1 KB
/
cbo_View_Merge
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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
视图合并(View Merge)：当SQL语句中有内联视图(in-line view，from后面的子查询)，或者SQL语句中有用 create view创建的视图，CBO会尝试将内联视图/视图拆开，
进行等价的改写，这个过程就叫做视图合并。
如果没有发生视图合并，在执行计划中，我们可以看到VIEW关键字，并且视图/子查询会作为一个整体。
如果发生了视图合并，那么视图/子查询就会被拆开，并且执行计划中视图/子查询部分就没有VIEW关键字。
视图合并(View Merge)的有可能好有可能坏。
####################################################333
1. 子查询非嵌套 - 针对 where 条件的半连接/反连接——exists / not exists(union/rownum/start with/cube)，目的干掉filter
2. 视图合并 没有(union/rownum/start with/cube)，默认会合并，合并之后，执行顺序会被改变，导致sql跑得慢
3. 谓词推入，重点高 视图当做 NL 被驱动表的情况，绝大部分是因为 驱动表 rows算错了。
##################################################
现有如下SQL及其执行计划(Oracle11.2.0.1
):
SQL> select a.*, c.grade
2 from (select ename, sal, a.deptno, b.dname
3 from emp a, dept b
4 where a.deptno = b.deptno) a,
5 salgrade c
6 where a.sal between c.losal and c.hisal;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3095952880
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 9 (23)|
| 1 | NESTED LOOPS | | | | |
| 2 | NESTED LOOPS | | 1 | 65 | 9 (23)|
| 3 | MERGE JOIN | | 1 | 52 | 8 (25)|
| 4 | SORT JOIN | | 5 | 195 | 4 (25)|
| 5 | TABLE ACCESS FULL | SALGRADE | 5 | 195 | 3 (0)|
|* 6 | FILTER | | | | |
|* 7 | SORT JOIN | | 14 | 182 | 4 (25)|
| 8 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)|
|* 9 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
| 10 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)|
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("SAL"<="C"."HISAL")
7 - access("SAL">="C"."LOSAL")
filter("SAL">="C"."LOSAL")
9 - access("A"."DEPTNO"="B"."DEPTNO")
SQL语句中有内联视图，但是执行计划中没有VIEW关键字，说明发生了视图合并。内联视图中emp表是与dept表关联的，但是执行计划中，emp表是与salgrade先关联的，
emp表与salgrade关联之后得到一个结果集再与dept表进行的关联，这说明发生了视图合并之后，有可能会打乱视图/子查询中表的原本的连接顺序。
现在添加hint no_merge(子查询别名/视图别名)禁止视图合并，再看执行计划：
SQL> select /*+ no_merge(a) */
2 a.*, c.grade
3 from (select ename, sal, a.deptno, b.dname
4 from emp a, dept b
5 where a.deptno = b.deptno) a,
6 salgrade c
7 where a.sal between c.losal and c.hisal;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4110645763
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 11 (28)|
| 1 | MERGE JOIN | | 1 | 81 | 11 (28)|
| 2 | SORT JOIN | | 5 | 195 | 4 (25)|
| 3 | TABLE ACCESS FULL | SALGRADE | 5 | 195 | 3 (0)|
|* 4 | FILTER | | | | |
|* 5 | SORT JOIN | | 14 | 588 | 7 (29)|
| 6 | VIEW | | 14 | 588 | 6 (17)|
| 7 | MERGE JOIN | | 14 | 364 | 6 (17)|
| 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)|
| 9 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|
|* 10 | SORT JOIN | | 14 | 182 | 4 (25)|
| 11 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."SAL"<="C"."HISAL")
5 - access("A"."SAL">="C"."LOSAL")
filter("A"."SAL">="C"."LOSAL")
10 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
执行计划中有VIEW关键字，并且emp是与dept进行关联的，这说明执行计划中没有发生视图合并。
也可以直接在子查询里面添加HINT no_merge禁止视图合并：
SQL> select a.*, c.grade
2 from (select /*+ no_merge */
3 ename, sal, a.deptno, b.dname
4 from emp a, dept b
5 where a.deptno = b.deptno) a,
6 salgrade c
7 where a.sal between c.losal and c.hisal;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4110645763
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 11 (28)|
| 1 | MERGE JOIN | | 1 | 81 | 11 (28)|
| 2 | SORT JOIN | | 5 | 195 | 4 (25)|
| 3 | TABLE ACCESS FULL | SALGRADE | 5 | 195 | 3 (0)|
|* 4 | FILTER | | | | |
|* 5 | SORT JOIN | | 14 | 588 | 7 (29)|
| 6 | VIEW | | 14 | 588 | 6 (17)|
| 7 | MERGE JOIN | | 14 | 364 | 6 (17)|
| 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)|
| 9 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|
|* 10 | SORT JOIN | | 14 | 182 | 4 (25)|
| 11 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."SAL"<="C"."HISAL")
5 - access("A"."SAL">="C"."LOSAL")
filter("A"."SAL">="C"."LOSAL")
10 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
当视图/子查询中有多个表关联，发生视图合并之后一般会将视图/子查询内部表关联顺序打乱。
或者这样改也可以，再子查询中加入 rownum>0
select a.*, c.grade
from (select
ename, sal, a.deptno, b.dname
from emp a, dept b
where a.deptno = b.deptno and rownum>0) a,
salgrade c
where a.sal between c.losal and c.hisal;
##当子查询中有rownum ,union , start with... cube ，就会当作一个整体。
有些读者可能遇到过类似案例，如下面SQL所示：
select ... from () a,() b where a.id=b.id;
单独执行子查询a，速度非常快，单独执行子查询b,速度也非常快，但是把上面2个子查询组合在一起，速度反而很慢，这就是典型的视图合并引起的性能问题。遇到类似问题，可以添加hint no_merge禁止视图合并，也可以让子查询a与子查询b进行hash连接，当子查询a与子查询b进行hash连接之后，就不会发生视图合并了：
select /*+ use_hash(a,b) */ ... from () a,() b where a.id=b.id;
为什么让子查询a与子查询b进行hash连接能使SQL变快呢？各位读者再回忆一下hash连接的算法，嵌套循环会传值(驱动表传值给被驱动表，通过连接列)，hash连接不会传值，因为hash连接不传值，所以当子查询a与子查询b进行hash连接之后，会自动的把子查询a与子查询b作为一个整体。
与子查询非嵌套一样，当视图中有固化子查询关键字的时候，就不能发生视图合并。
固化子查询关键字包括(union/union all/start with connect by/rownum/cube/rollup)。
现在对示例SQL添加union all，查看SQL执行计划：
SQL> select a.*, c.grade
2 from (select ename, sal, a.deptno, b.dname
3 from emp a, dept b
4 where a.deptno = b.deptno
5 union all
6 select 'SMITH', 1600, 10, 'ACCOUNTING' from dual) a,
7 salgrade c
8 where a.sal between c.losal and c.hisal;
15 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1428389312
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 13 (24)|
| 1 | MERGE JOIN | | 1 | 81 | 13 (24)|
| 2 | SORT JOIN | | 5 | 195 | 4 (25)|
| 3 | TABLE ACCESS FULL | SALGRADE | 5 | 195 | 3 (0)|
|* 4 | FILTER | | | | |
|* 5 | SORT JOIN | | 15 | 630 | 9 (23)|
| 6 | VIEW | | 15 | 630 | 8 (13)|
| 7 | UNION-ALL | | | | |
| 8 | MERGE JOIN | | 14 | 364 | 6 (17)|
| 9 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)|
| 10 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|
|* 11 | SORT JOIN | | 14 | 182 | 4 (25)|
| 12 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)|
| 13 | FAST DUAL | | 1 | | 2 (0)|
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."SAL"<="C"."HISAL")
5 - access("A"."SAL">="C"."LOSAL")
filter("A"."SAL">="C"."LOSAL")
11 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
从执行计划中可以看到，添加了union all之后，子查询被固化，没有发生视图合并。
现在对SQL添加rownum，查看SQL执行计划：
SQL> select a.*, c.grade
2 from (select ename, sal, a.deptno, b.dname
3 from emp a, dept b
4 where a.deptno = b.deptno
5 and rownum >= 1) a,
6 salgrade c
7 where a.sal between c.losal and c.hisal;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 819637296
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 11 (28)|
| 1 | MERGE JOIN | | 1 | 72 | 11 (28)|
| 2 | SORT JOIN | | 5 | 195 | 4 (25)|
| 3 | TABLE ACCESS FULL | SALGRADE | 5 | 195 | 3 (0)|
|* 4 | FILTER | | | | |
|* 5 | SORT JOIN | | 14 | 462 | 7 (29)|
| 6 | VIEW | | 14 | 462 | 6 (17)|
| 7 | COUNT | | | | |
|* 8 | FILTER | | | | |
| 9 | MERGE JOIN | | 14 | 364 | 6 (17)|
| 10 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)|
| 11 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|
|* 12 | SORT JOIN | | 14 | 182 | 4 (25)|
| 13 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)|
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."SAL"<="C"."HISAL")
5 - access("A"."SAL">="C"."LOSAL")
filter("A"."SAL">="C"."LOSAL")
8 - filter(ROWNUM>=1)
12 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
从执行计划中可以看到，添加了rownum之后，子查询同样被固化，没有发生视图合并。
##########################################
视图 合并 针对 哪里的 子查询的？
from 后面（内联）
子查询非浅套针对哪里的子查询？
where
##########################################