在Oracle中，执行计划里的access和filter有什么区别？
如下所示：
1Predicate Information (identified by operation id):
2---------------------------------------------------
3 4 - access("A"."EMPNO"="B"."MGR")
4 filter("A"."EMPNO"="B"."MGR")
5 5 - filter("B"."MGR" IS NOT NULL)
一般而言，access表示这个谓词条件的值将会影响数据的访问路径（表还是索引）；filter表示谓词条件的值不会影响数据的访问路劲，只起到过滤的作用。NOT IN或MIN函数等容易产生filter操作。
对于filter而言如果只有一个子节点，那么就是简单过滤操作（独立操作）。如果有两个或更多子节点，那么就是类似Nested Loops操作，只不过与Nested Loops差别在于，filter内部会构建HASH表，对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，那么，filter操作将是严重影响性能的操作，可能会导致目标SQL几天都执行不完。
下面看看各种情况下的FILTER操作：
（一）单子节点：
1LHR@orclasm > set autot on
2LHR@orclasm > SELECT T.JOB, COUNT(1) FROM SCOTT.EMP T GROUP BY T.JOB HAVING COUNT(1)>3;
3
4JOB COUNT(1)
5--------- ----------
6CLERK 4
7SALESMAN 4
8
9
10Execution Plan
11----------------------------------------------------------
12Plan hash value: 2138686577
13
14----------------------------------------------------------------------------
15| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
16----------------------------------------------------------------------------
17| 0 | SELECT STATEMENT | | 1 | 8 | 4 (25)| 00:00:01 |
18|* 1 | FILTER | | | | | |
19| 2 | HASH GROUP BY | | 1 | 8 | 4 (25)| 00:00:01 |
20| 3 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
21----------------------------------------------------------------------------
22
23Predicate Information (identified by operation id):
24---------------------------------------------------
25
26 1 - filter(COUNT(*)>3)
27
28
29Statistics
30----------------------------------------------------------
31 25 recursive calls
32 4 db block gets
33 6 consistent gets
34 0 physical reads
35 1544 redo size
36 660 bytes sent via SQL*Net to client
37 519 bytes received via SQL*Net from client
38 2 SQL*Net roundtrips to/from client
39 0 sorts (memory)
40 0 sorts (disk)
41 2 rows processed
很显然ID1的filter操作只有一个子节点ID2，在这种情况下的filter操作也就是单纯的过滤操作。
（二）多子节点：
filter多子节点往往就是性能杀手，主要出现在子查询无法UNNEST查询转换，经常遇到的情况就是NOT IN子查询、子查询和OR连用、复杂子查询等情况。
1DROP TABLE T_20170703_LHR_01 PURGE;
2DROP TABLE T_20170703_LHR_02 PURGE;
3DROP TABLE T_20170703_LHR_03 PURGE;
4CREATE TABLE T_20170703_LHR_01 AS SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS;
5CREATE TABLE T_20170703_LHR_02 AS SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS;
6CREATE TABLE T_20170703_LHR_03 AS SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS WHERE ROWNUM <=1000;
7CREATE INDEX IND_T1_OBJ_ID_1 ON T_20170703_LHR_01(OBJECT_ID);
8CREATE INDEX IND_T2_OBJ_ID_1 ON T_20170703_LHR_02(OBJECT_ID);
9CREATE INDEX IND_T3_OBJ_ID_1 ON T_20170703_LHR_03(OBJECT_ID);
10EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t_20170703_LHR_01');
11EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t_20170703_LHR_02');
12EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t_20170703_LHR_03');
13
14ALTER SESSION SET STATISTICS_LEVEL=ALL;
15SELECT COUNT(1)
16 FROM T_20170703_LHR_01 T1
17 WHERE T1.OBJECT_TYPE = 'TABLE'
18 OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID)
19 OR EXISTS (SELECT 1 FROM T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID);
20SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
21
22SELECT COUNT(1)
23 FROM (SELECT T1.*
24 FROM T_20170703_LHR_01 T1
25 WHERE T1.OBJECT_TYPE = 'TABLE'
26 UNION ALL (SELECT T1.*
27 FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
28 WHERE T1.OBJECT_ID = T2.OBJECT_ID
29 AND T1.OBJECT_TYPE <> 'TABLE'
30 UNION
31 SELECT T1.*
32 FROM T_20170703_LHR_01 T1, T_20170703_LHR_03 T3
33 WHERE T1.OBJECT_ID = T3.OBJECT_ID
34 AND T1.OBJECT_TYPE <> 'TABLE'));
35SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
36
37
38WITH TMP_T1 AS
39 (SELECT T1.* FROM T_20170703_LHR_01 T1)
40SELECT COUNT(1)
41 FROM (SELECT T1.*
42 FROM TMP_T1 T1
43 WHERE T1.OBJECT_TYPE = 'TABLE'
44 UNION ALL (SELECT T1.*
45 FROM TMP_T1 T1, T_20170703_LHR_02 T2
46 WHERE T1.OBJECT_ID = T2.OBJECT_ID
47 AND T1.OBJECT_TYPE <> 'TABLE'
48 UNION
49 SELECT T1.*
50 FROM TMP_T1 T1, T_20170703_LHR_03 T3
51 WHERE T1.OBJECT_ID = T3.OBJECT_ID
52 AND T1.OBJECT_TYPE <> 'TABLE'));
53SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
具体执行计划：
1LHR@orclasm > ALTER SESSION SET STATISTICS_LEVEL=ALL;
2
3Session altered.
4
5LHR@orclasm > SELECT COUNT(1)
6 2 FROM T_20170703_LHR_01 T1
7 3 WHERE T1.OBJECT_TYPE = 'TABLE'
8 4 OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID)
9 5 OR EXISTS (SELECT 1 FROM T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID);
10
11 COUNT(1)
12----------
13 5060
14
15LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
16
17PLAN_TABLE_OUTPUT
18------------------------------------------------------------------------------------
19SQL_ID 5894cbw5v4mpj, child number 0
20-------------------------------------
21SELECT COUNT(1) FROM T_20170703_LHR_01 T1 WHERE T1.OBJECT_TYPE =
22'TABLE' OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE
23T1.OBJECT_ID = T2.OBJECT_ID) OR EXISTS (SELECT 1 FROM
24T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID)
25
26Plan hash value: 1566256780
27
28---------------------------------------------------------------------------------------------------
29| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
30---------------------------------------------------------------------------------------------------
31| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.17 | 149K|
32| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.17 | 149K|
33|* 2 | FILTER | | 1 | | 5060 |00:00:00.17 | 149K|
34| 3 | TABLE ACCESS FULL| T_20170703_LHR_01 | 1 | 78271 | 78271 |00:00:00.01 | 469 |
35|* 4 | INDEX RANGE SCAN | IND_T2_OBJ_ID_1 | 74941 | 1 | 1741 |00:00:00.05 | 75356 |
36|* 5 | INDEX RANGE SCAN | IND_T3_OBJ_ID_1 | 73200 | 1 | 0 |00:00:00.05 | 73308 |
37---------------------------------------------------------------------------------------------------
38
39Predicate Information (identified by operation id):
40---------------------------------------------------
41
42 2 - filter(("T1"."OBJECT_TYPE"='TABLE' OR IS NOT NULL OR IS NOT NULL))
43 4 - access("T2"."OBJECT_ID"=:B1)
44 5 - access("T3"."OBJECT_ID"=:B1)
45
46
4727 rows selected.
48
49LHR@orclasm >
50LHR@orclasm >
51LHR@orclasm > SELECT COUNT(1)
52 2 FROM (SELECT T1.*
53 3 FROM T_20170703_LHR_01 T1
54 4 WHERE T1.OBJECT_TYPE = 'TABLE'
55 5 UNION ALL (SELECT T1.*
56 6 FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
57 7 WHERE T1.OBJECT_ID = T2.OBJECT_ID
58 8 AND T1.OBJECT_TYPE <> 'TABLE'
59 9 UNION
60 10 SELECT T1.*
61 11 FROM T_20170703_LHR_01 T1, T_20170703_LHR_03 T3
62 12 WHERE T1.OBJECT_ID = T3.OBJECT_ID
63 13 AND T1.OBJECT_TYPE <> 'TABLE'));
64
65 COUNT(1)
66----------
67 5060
68
69LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
70
71PLAN_TABLE_OUTPUT
72-----------------------------------------------------------------------------------
73SQL_ID 5n0xpnt0gzb0d, child number 0
74-------------------------------------
75SELECT COUNT(1) FROM (SELECT T1.* FROM T_20170703_LHR_01 T1
76 WHERE T1.OBJECT_TYPE = 'TABLE' UNION ALL (SELECT T1.*
77 FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
78 WHERE T1.OBJECT_ID = T2.OBJECT_ID
79AND T1.OBJECT_TYPE <> 'TABLE' UNION
80SELECT T1.* FROM T_20170703_LHR_01 T1,
81T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID =
82T3.OBJECT_ID AND T1.OBJECT_TYPE <> 'TABLE'))
83
84Plan hash value: 3651740877
85
86-------------------------------------------------------------------------------------------------------------------------------------
87| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
88-------------------------------------------------------------------------------------------------------------------------------------
89| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.05 | 1423 | | | |
90| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.05 | 1423 | | | |
91| 2 | VIEW | | 1 | 4641 | 5060 |00:00:00.05 | 1423 | | | |
92| 3 | UNION-ALL | | 1 | | 5060 |00:00:00.05 | 1423 | | | |
93|* 4 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 1631 | 3319 |00:00:00.01 | 469 | | | |
94| 5 | SORT UNIQUE | | 1 | 4641 | 1741 |00:00:00.05 | 954 | 178K| 178K| 158K (0)|
95| 6 | UNION-ALL | | 1 | | 2634 |00:00:00.05 | 954 | | | |
96|* 7 | HASH JOIN | | 1 | 2010 | 1741 |00:00:00.03 | 479 | 1452K| 1452K| 1667K (0)|
97| 8 | INDEX FAST FULL SCAN| IND_T2_OBJ_ID_1 | 1 | 2012 | 2010 |00:00:00.01 | 10 | | | |
98|* 9 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 76640 | 74952 |00:00:00.01 | 469 | | | |
99|* 10 | HASH JOIN | | 1 | 1000 | 893 |00:00:00.02 | 475 | 1452K| 1452K| 1571K (0)|
100| 11 | INDEX FAST FULL SCAN| IND_T3_OBJ_ID_1 | 1 | 1000 | 1000 |00:00:00.01 | 6 | | | |
101|* 12 | TABLE ACCESS FULL | T_20170703_LHR_01 | 1 | 76640 | 74952 |00:00:00.01 | 469 | | | |
102-------------------------------------------------------------------------------------------------------------------------------------
103
104Predicate Information (identified by operation id):
105---------------------------------------------------
106
107 4 - filter("T1"."OBJECT_TYPE"='TABLE')
108 7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
109 9 - filter("T1"."OBJECT_TYPE"<>'TABLE')
110 10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
111 12 - filter("T1"."OBJECT_TYPE"<>'TABLE')
112
113
11440 rows selected.
115
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：230161599
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
最后修改时间：2020-01-10 20:56:58
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
