116LHR@orclasm >
117LHR@orclasm >
118LHR@orclasm >
119LHR@orclasm > WITH TMP_T1 AS
120 2 (SELECT T1.* FROM T_20170703_LHR_01 T1)
121 3 SELECT COUNT(1)
122 4 FROM (SELECT T1.*
123 5 FROM TMP_T1 T1
124 6 WHERE T1.OBJECT_TYPE = 'TABLE'
125 7 UNION ALL (SELECT T1.*
126 8 FROM TMP_T1 T1, T_20170703_LHR_02 T2
127 9 WHERE T1.OBJECT_ID = T2.OBJECT_ID
128 10 AND T1.OBJECT_TYPE <> 'TABLE'
129 11 UNION
130 12 SELECT T1.*
131 13 FROM TMP_T1 T1, T_20170703_LHR_03 T3
132 14 WHERE T1.OBJECT_ID = T3.OBJECT_ID
133 15 AND T1.OBJECT_TYPE <> 'TABLE'));
134
135 COUNT(1)
136----------
137 5060
138
139LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
140
141PLAN_TABLE_OUTPUT
142-----------------------------------------------------------------------------------
143SQL_ID 9wy6ds1m0fmta, child number 0
144-------------------------------------
145WITH TMP_T1 AS (SELECT T1.* FROM T_20170703_LHR_01 T1) SELECT COUNT(1)
146 FROM (SELECT T1.* FROM TMP_T1 T1 WHERE
147T1.OBJECT_TYPE = 'TABLE' UNION ALL (SELECT T1.*
148 FROM TMP_T1 T1, T_20170703_LHR_02 T2 WHERE
149T1.OBJECT_ID = T2.OBJECT_ID AND T1.OBJECT_TYPE <>
150'TABLE' UNION SELECT T1.*
151 FROM TMP_T1 T1, T_20170703_LHR_03 T3 WHERE
152T1.OBJECT_ID = T3.OBJECT_ID AND T1.OBJECT_TYPE <>
153'TABLE'))
154
155Plan hash value: 3651740877
156
157-------------------------------------------------------------------------------------------------------------------------------------
158| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
159-------------------------------------------------------------------------------------------------------------------------------------
160| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 | 1423 | | | |
161| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 | 1423 | | | |
162| 2 | VIEW | | 1 | 4641 | 5060 |00:00:00.07 | 1423 | | | |
163| 3 | UNION-ALL | | 1 | | 5060 |00:00:00.07 | 1423 | | | |
164|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 1631 | 3319 |00:00:00.01 | 469 | | | |
165| 5 | SORT UNIQUE | | 1 | 4641 | 1741 |00:00:00.07 | 954 | 178K| 178K| 158K (0)|
166| 6 | UNION-ALL | | 1 | | 2634 |00:00:00.07 | 954 | | | |
167|* 7 | HASH JOIN | | 1 | 2010 | 1741 |00:00:00.04 | 479 | 1452K| 1452K| 1620K (0)|
168| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 1 | 2012 | 2010 |00:00:00.01 | 10 | | | |
169|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 76640 | 74952 |00:00:00.01 | 469 | | | |
170|* 10 | HASH JOIN | | 1 | 1000 | 893 |00:00:00.03 | 475 | 1452K| 1452K| 1524K (0)|
171| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1 | 1000 | 1000 |00:00:00.01 | 6 | | | |
172|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 76640 | 74952 |00:00:00.01 | 469 | | | |
173-------------------------------------------------------------------------------------------------------------------------------------
174
175Predicate Information (identified by operation id):
176---------------------------------------------------
177
178 4 - filter("T1"."OBJECT_TYPE"='TABLE')
179 7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
180 9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
181 10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
182 12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
183
184
18541 rows selected.
186
187LHR@orclasm >
188LHR@orclasm > set autot on
189LHR@orclasm > SELECT COUNT(1)
190 2 FROM T_20170703_LHR_01 T1
191 3 WHERE T1.OBJECT_TYPE = 'TABLE'
192 4 OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID)
193 5 OR EXISTS (SELECT 1 FROM T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID);
194
195 COUNT(1)
196----------
197 5060
198
199
200Execution Plan
201----------------------------------------------------------
202Plan hash value: 1566256780
203
204-----------------------------------------------------------------------------------------
205| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
206-----------------------------------------------------------------------------------------
207| 0 | SELECT STATEMENT | | 1 | 14 | 133 (1)| 00:00:02 |
208| 1 | SORT AGGREGATE | | 1 | 14 | | |
209|* 2 | FILTER | | | | | |
210| 3 | TABLE ACCESS FULL| T_20170703_LHR_01 | 78271 | 1070K| 133 (1)| 00:00:02 |
211|* 4 | INDEX RANGE SCAN | IND_T2_OBJ_ID_1 | 1 | 5 | 1 (0)| 00:00:01 |
212|* 5 | INDEX RANGE SCAN | IND_T3_OBJ_ID_1 | 1 | 5 | 1 (0)| 00:00:01 |
213-----------------------------------------------------------------------------------------
214
215Predicate Information (identified by operation id):
216---------------------------------------------------
217
218 2 - filter("T1"."OBJECT_TYPE"='TABLE' OR EXISTS (SELECT 0 FROM
219 "T_20170703_LHR_02" "T2" WHERE "T2"."OBJECT_ID"=:B1) OR EXISTS (SELECT 0 FROM
220 "T_20170703_LHR_03" "T3" WHERE "T3"."OBJECT_ID"=:B2))
221 4 - access("T2"."OBJECT_ID"=:B1)
222 5 - access("T3"."OBJECT_ID"=:B1)
223
224
225Statistics
226----------------------------------------------------------
227 0 recursive calls
228 0 db block gets
229 149133 consistent gets
230 0 physical reads
231 0 redo size
232 527 bytes sent via SQL*Net to client
233 520 bytes received via SQL*Net from client
234 2 SQL*Net roundtrips to/from client
235 0 sorts (memory)
236 0 sorts (disk)
237 1 rows processed
238
239LHR@orclasm > SELECT COUNT(1)
240 2 FROM (SELECT T1.*
241 3 FROM T_20170703_LHR_01 T1
242 4 WHERE T1.OBJECT_TYPE = 'TABLE'
243 5 UNION ALL (SELECT T1.*
244 6 FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
245 7 WHERE T1.OBJECT_ID = T2.OBJECT_ID
246 8 AND T1.OBJECT_TYPE <> 'TABLE'
247 9 UNION
248 10 SELECT T1.*
249 11 FROM T_20170703_LHR_01 T1, T_20170703_LHR_03 T3
250 12 WHERE T1.OBJECT_ID = T3.OBJECT_ID
251 13 AND T1.OBJECT_TYPE <> 'TABLE'));
252
253 COUNT(1)
254----------
255 5060
256
257
258Execution Plan
259----------------------------------------------------------
260Plan hash value: 3651740877
261
262------------------------------------------------------------------------------------------------
263| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
264------------------------------------------------------------------------------------------------
265| 0 | SELECT STATEMENT | | 1 | | 407 (2)| 00:00:05 |
266| 1 | SORT AGGREGATE | | 1 | | | |
267| 2 | VIEW | | 4641 | | 407 (2)| 00:00:05 |
268| 3 | UNION-ALL | | | | | |
269|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1631 | 61978 | 133 (1)| 00:00:02 |
270| 5 | SORT UNIQUE | | 4641 | 186K| 407 (68)| 00:00:05 |
271| 6 | UNION-ALL | | | | | |
272|* 7 | HASH JOIN | | 2010 | 86430 | 137 (2)| 00:00:02 |
273| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 2012 | 10060 | 3 (0)| 00:00:01 |
274|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
275|* 10 | HASH JOIN | | 1000 | 43000 | 137 (2)| 00:00:02 |
276| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1000 | 5000 | 3 (0)| 00:00:01 |
277|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
278------------------------------------------------------------------------------------------------
279
280Predicate Information (identified by operation id):
281---------------------------------------------------
282
283 4 - filter("T1"."OBJECT_TYPE"='TABLE')
284 7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
285 9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
286 10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
287 12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
288
289
290Statistics
291----------------------------------------------------------
292 0 recursive calls
293 0 db block gets
294 1423 consistent gets
295 0 physical reads
296 0 redo size
297 527 bytes sent via SQL*Net to client
298 520 bytes received via SQL*Net from client
299 2 SQL*Net roundtrips to/from client
300 1 sorts (memory)
301 0 sorts (disk)
302 1 rows processed
303
304LHR@orclasm > WITH TMP_T1 AS
305 2 (SELECT T1.* FROM T_20170703_LHR_01 T1)
306 3 SELECT COUNT(1)
307 4 FROM (SELECT T1.*
308 5 FROM TMP_T1 T1
309 6 WHERE T1.OBJECT_TYPE = 'TABLE'
310 7 UNION ALL (SELECT T1.*
311 8 FROM TMP_T1 T1, T_20170703_LHR_02 T2
312 9 WHERE T1.OBJECT_ID = T2.OBJECT_ID
313 10 AND T1.OBJECT_TYPE <> 'TABLE'
314 11 UNION
315 12 SELECT T1.*
316 13 FROM TMP_T1 T1, T_20170703_LHR_03 T3
317 14 WHERE T1.OBJECT_ID = T3.OBJECT_ID
318 15 AND T1.OBJECT_TYPE <> 'TABLE'));
319
320 COUNT(1)
321----------
322 5060
323
324
325Execution Plan
326----------------------------------------------------------
327Plan hash value: 3651740877
328
329------------------------------------------------------------------------------------------------
330| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
331------------------------------------------------------------------------------------------------
332| 0 | SELECT STATEMENT | | 1 | | 407 (2)| 00:00:05 |
333| 1 | SORT AGGREGATE | | 1 | | | |
334| 2 | VIEW | | 4641 | | 407 (2)| 00:00:05 |
335| 3 | UNION-ALL | | | | | |
336|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1631 | 61978 | 133 (1)| 00:00:02 |
337| 5 | SORT UNIQUE | | 4641 | 186K| 407 (68)| 00:00:05 |
338| 6 | UNION-ALL | | | | | |
339|* 7 | HASH JOIN | | 2010 | 86430 | 137 (2)| 00:00:02 |
340| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 2012 | 10060 | 3 (0)| 00:00:01 |
341|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
342|* 10 | HASH JOIN | | 1000 | 43000 | 137 (2)| 00:00:02 |
343| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1000 | 5000 | 3 (0)| 00:00:01 |
344|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
345------------------------------------------------------------------------------------------------
346
347Predicate Information (identified by operation id):
348---------------------------------------------------
349
350 4 - filter("T1"."OBJECT_TYPE"='TABLE')
351 7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
352 9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
353 10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
354 12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
355
356
357Statistics
358----------------------------------------------------------
359 0 recursive calls
360 0 db block gets
361 1423 consistent gets
362 0 physical reads
363 0 redo size
364 527 bytes sent via SQL*Net to client
365 520 bytes received via SQL*Net from client
366 2 SQL*Net roundtrips to/from client
367 1 sorts (memory)
368 0 sorts (disk)
369 1 rows processed
& 说明：
有关access和filter的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2141522/
最后修改时间：2020-01-10 20:56:57
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
