【DB笔试面试258】在Oracle中，执行计划里的access和filter有什么区别（下）？
【DB笔试面试258】在Oracle中，执行计划里的access和filter有什么区别（下）？
AiDBA宝典
发布于 2019-09-30 16:18:14
发布于 2019-09-30 16:18:14
文章被收录于专栏：小麦苗的DB宝专栏
LHR@orclasm >
LHR@orclasm >
LHR@orclasm >
LHR@orclasm > WITH TMP_T1 AS
2 (SELECT T1.* FROM T_20170703_LHR_01 T1)
3 SELECT COUNT(1)
4 FROM (SELECT T1.*
5 FROM TMP_T1 T1
6 WHERE T1.OBJECT_TYPE = 'TABLE'
7 UNION ALL (SELECT T1.*
8 FROM TMP_T1 T1, T_20170703_LHR_02 T2
9 WHERE T1.OBJECT_ID = T2.OBJECT_ID
10 AND T1.OBJECT_TYPE <> 'TABLE'
11 UNION
12 SELECT T1.*
13 FROM TMP_T1 T1, T_20170703_LHR_03 T3
14 WHERE T1.OBJECT_ID = T3.OBJECT_ID
15 AND T1.OBJECT_TYPE <> 'TABLE'));
COUNT(1)
----------
5060
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 9wy6ds1m0fmta, child number 0
-------------------------------------
WITH TMP_T1 AS (SELECT T1.* FROM T_20170703_LHR_01 T1) SELECT COUNT(1)
FROM (SELECT T1.* FROM TMP_T1 T1 WHERE
T1.OBJECT_TYPE = 'TABLE' UNION ALL (SELECT T1.*
FROM TMP_T1 T1, T_20170703_LHR_02 T2 WHERE
T1.OBJECT_ID = T2.OBJECT_ID AND T1.OBJECT_TYPE <>
'TABLE' UNION SELECT T1.*
FROM TMP_T1 T1, T_20170703_LHR_03 T3 WHERE
T1.OBJECT_ID = T3.OBJECT_ID AND T1.OBJECT_TYPE <>
'TABLE'))
Plan hash value: 3651740877
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 | 1423 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 | 1423 | | | |
| 2 | VIEW | | 1 | 4641 | 5060 |00:00:00.07 | 1423 | | | |
| 3 | UNION-ALL | | 1 | | 5060 |00:00:00.07 | 1423 | | | |
|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 1631 | 3319 |00:00:00.01 | 469 | | | |
| 5 | SORT UNIQUE | | 1 | 4641 | 1741 |00:00:00.07 | 954 | 178K| 178K| 158K (0)|
| 6 | UNION-ALL | | 1 | | 2634 |00:00:00.07 | 954 | | | |
|* 7 | HASH JOIN | | 1 | 2010 | 1741 |00:00:00.04 | 479 | 1452K| 1452K| 1620K (0)|
| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 1 | 2012 | 2010 |00:00:00.01 | 10 | | | |
|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 76640 | 74952 |00:00:00.01 | 469 | | | |
|* 10 | HASH JOIN | | 1 | 1000 | 893 |00:00:00.03 | 475 | 1452K| 1452K| 1524K (0)|
| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1 | 1000 | 1000 |00:00:00.01 | 6 | | | |
|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 76640 | 74952 |00:00:00.01 | 469 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OBJECT_TYPE"='TABLE')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
41 rows selected.
LHR@orclasm >
LHR@orclasm > set autot on
LHR@orclasm > SELECT COUNT(1)
2 FROM T_20170703_LHR_01 T1
3 WHERE T1.OBJECT_TYPE = 'TABLE'
4 OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID)
5 OR EXISTS (SELECT 1 FROM T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID);
COUNT(1)
----------
5060
Execution Plan
----------------------------------------------------------
Plan hash value: 1566256780
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 133 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| T_20170703_LHR_01 | 78271 | 1070K| 133 (1)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | IND_T2_OBJ_ID_1 | 1 | 5 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_T3_OBJ_ID_1 | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."OBJECT_TYPE"='TABLE' OR EXISTS (SELECT 0 FROM
"T_20170703_LHR_02" "T2" WHERE "T2"."OBJECT_ID"=:B1) OR EXISTS (SELECT 0 FROM
"T_20170703_LHR_03" "T3" WHERE "T3"."OBJECT_ID"=:B2))
4 - access("T2"."OBJECT_ID"=:B1)
5 - access("T3"."OBJECT_ID"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
149133 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > SELECT COUNT(1)
2 FROM (SELECT T1.*
3 FROM T_20170703_LHR_01 T1
4 WHERE T1.OBJECT_TYPE = 'TABLE'
5 UNION ALL (SELECT T1.*
6 FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
7 WHERE T1.OBJECT_ID = T2.OBJECT_ID
8 AND T1.OBJECT_TYPE <> 'TABLE'
9 UNION
10 SELECT T1.*
11 FROM T_20170703_LHR_01 T1, T_20170703_LHR_03 T3
12 WHERE T1.OBJECT_ID = T3.OBJECT_ID
13 AND T1.OBJECT_TYPE <> 'TABLE'));
COUNT(1)
----------
5060
Execution Plan
----------------------------------------------------------
Plan hash value: 3651740877
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 407 (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 4641 | | 407 (2)| 00:00:05 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1631 | 61978 | 133 (1)| 00:00:02 |
| 5 | SORT UNIQUE | | 4641 | 186K| 407 (68)| 00:00:05 |
| 6 | UNION-ALL | | | | | |
|* 7 | HASH JOIN | | 2010 | 86430 | 137 (2)| 00:00:02 |
| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 2012 | 10060 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
|* 10 | HASH JOIN | | 1000 | 43000 | 137 (2)| 00:00:02 |
| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1000 | 5000 | 3 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OBJECT_TYPE"='TABLE')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1423 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > WITH TMP_T1 AS
2 (SELECT T1.* FROM T_20170703_LHR_01 T1)
3 SELECT COUNT(1)
4 FROM (SELECT T1.*
5 FROM TMP_T1 T1
6 WHERE T1.OBJECT_TYPE = 'TABLE'
7 UNION ALL (SELECT T1.*
8 FROM TMP_T1 T1, T_20170703_LHR_02 T2
9 WHERE T1.OBJECT_ID = T2.OBJECT_ID
10 AND T1.OBJECT_TYPE <> 'TABLE'
11 UNION
12 SELECT T1.*
13 FROM TMP_T1 T1, T_20170703_LHR_03 T3
14 WHERE T1.OBJECT_ID = T3.OBJECT_ID
15 AND T1.OBJECT_TYPE <> 'TABLE'));
COUNT(1)
----------
5060
Execution Plan
----------------------------------------------------------
Plan hash value: 3651740877
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 407 (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 4641 | | 407 (2)| 00:00:05 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1631 | 61978 | 133 (1)| 00:00:02 |
| 5 | SORT UNIQUE | | 4641 | 186K| 407 (68)| 00:00:05 |
| 6 | UNION-ALL | | | | | |
|* 7 | HASH JOIN | | 2010 | 86430 | 137 (2)| 00:00:02 |
| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 2012 | 10060 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
|* 10 | HASH JOIN | | 1000 | 43000 | 137 (2)| 00:00:02 |
| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1000 | 5000 | 3 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 76640 | 2844K| 133 (1)| 00:00:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OBJECT_TYPE"='TABLE')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1423 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
& 说明：
有关access和filter的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2141522/
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2019-01-18，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
