题目如下所示：
在Oracle中，执行计划里的access和filter有什么区别？
答案如下所示：
如下所示：
代码语言：javascript复制Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."EMPNO"="B"."MGR")
filter("A"."EMPNO"="B"."MGR")
5 - filter("B"."MGR" IS NOT NULL)
一般而言，access表示这个谓词条件的值将会影响数据的访问路径（表还是索引）；filter表示谓词条件的值不会影响数据的访问路劲，只起到过滤的作用。NOT IN或MIN函数等容易产生filter操作。
对于filter而言如果只有一个子节点，那么就是简单过滤操作（独立操作）。如果有两个或更多子节点，那么就是类似Nested Loops操作，只不过与Nested Loops差别在于，filter内部会构建HASH表，对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，那么，filter操作将是严重影响性能的操作，可能会导致目标SQL几天都执行不完。
下面看看各种情况下的FILTER操作：
（一）单子节点：
代码语言：javascript复制LHR@orclasm > set autot on
LHR@orclasm > SELECT T.JOB, COUNT(1) FROM SCOTT.EMP T GROUP BY T.JOB HAVING COUNT(1)>3;
JOB COUNT(1)
--------- ----------
CLERK 4
SALESMAN 4
Execution Plan
----------------------------------------------------------
Plan hash value: 2138686577
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 8 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>3)
Statistics
----------------------------------------------------------
25 recursive calls
4 db block gets
6 consistent gets
0 physical reads
1544 redo size
660 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
很显然ID1的filter操作只有一个子节点ID2，在这种情况下的filter操作也就是单纯的过滤操作。
（二）多子节点：
filter多子节点往往就是性能杀手，主要出现在子查询无法UNNEST查询转换，经常遇到的情况就是NOT IN子查询、子查询和OR连用、复杂子查询等情况。
代码语言：javascript复制DROP TABLE T_20170703_LHR_01 PURGE;
DROP TABLE T_20170703_LHR_02 PURGE;
DROP TABLE T_20170703_LHR_03 PURGE;
CREATE TABLE T_20170703_LHR_01 AS SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS;
CREATE TABLE T_20170703_LHR_02 AS SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS;
CREATE TABLE T_20170703_LHR_03 AS SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS WHERE ROWNUM <=1000;
CREATE INDEX IND_T1_OBJ_ID_1 ON T_20170703_LHR_01(OBJECT_ID);
CREATE INDEX IND_T2_OBJ_ID_1 ON T_20170703_LHR_02(OBJECT_ID);
CREATE INDEX IND_T3_OBJ_ID_1 ON T_20170703_LHR_03(OBJECT_ID);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t_20170703_LHR_01');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t_20170703_LHR_02');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t_20170703_LHR_03');
ALTER SESSION SET STATISTICS_LEVEL=ALL;
SELECT COUNT(1)
FROM T_20170703_LHR_01 T1
WHERE T1.OBJECT_TYPE = 'TABLE'
OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE T1.OBJECT_ID = T2.OBJECT_ID)
OR EXISTS (SELECT 1 FROM T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
SELECT COUNT(1)
FROM (SELECT T1.*
FROM T_20170703_LHR_01 T1
WHERE T1.OBJECT_TYPE = 'TABLE'
UNION ALL (SELECT T1.*
FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
WHERE T1.OBJECT_ID = T2.OBJECT_ID
AND T1.OBJECT_TYPE <> 'TABLE'
UNION
SELECT T1.*
FROM T_20170703_LHR_01 T1, T_20170703_LHR_03 T3
WHERE T1.OBJECT_ID = T3.OBJECT_ID
AND T1.OBJECT_TYPE <> 'TABLE'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
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'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
具体执行计划：
代码语言：javascript复制LHR@orclasm > ALTER SESSION SET STATISTICS_LEVEL=ALL;
Session altered.
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
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 5894cbw5v4mpj, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_20170703_LHR_01 T1 WHERE T1.OBJECT_TYPE =
'TABLE' OR EXISTS (SELECT 1 FROM T_20170703_LHR_02 T2 WHERE
T1.OBJECT_ID = T2.OBJECT_ID) OR EXISTS (SELECT 1 FROM
T_20170703_LHR_03 T3 WHERE T1.OBJECT_ID = T3.OBJECT_ID)
Plan hash value: 1566256780
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.17 | 149K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.17 | 149K|
|* 2 | FILTER | | 1 | | 5060 |00:00:00.17 | 149K|
| 3 | TABLE ACCESS FULL| T_20170703_LHR_01 | 1 | 78271 | 78271 |00:00:00.01 | 469 |
|* 4 | INDEX RANGE SCAN | IND_T2_OBJ_ID_1 | 74941 | 1 | 1741 |00:00:00.05 | 75356 |
|* 5 | INDEX RANGE SCAN | IND_T3_OBJ_ID_1 | 73200 | 1 | 0 |00:00:00.05 | 73308 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T1"."OBJECT_TYPE"='TABLE' OR IS NOT NULL OR IS NOT NULL))
4 - access("T2"."OBJECT_ID"=:B1)
5 - access("T3"."OBJECT_ID"=:B1)
27 rows selected.
LHR@orclasm >
LHR@orclasm >
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
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 5n0xpnt0gzb0d, child number 0
-------------------------------------
SELECT COUNT(1) FROM (SELECT T1.* FROM T_20170703_LHR_01 T1
WHERE T1.OBJECT_TYPE = 'TABLE' UNION ALL (SELECT T1.*
FROM T_20170703_LHR_01 T1, T_20170703_LHR_02 T2
WHERE T1.OBJECT_ID = T2.OBJECT_ID
AND T1.OBJECT_TYPE <> 'TABLE' UNION
SELECT T1.* FROM T_20170703_LHR_01 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.05 | 1423 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.05 | 1423 | | | |
| 2 | VIEW | | 1 | 4641 | 5060 |00:00:00.05 | 1423 | | | |
| 3 | UNION-ALL | | 1 | | 5060 |00:00:00.05 | 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.05 | 954 | 178K| 178K| 158K (0)|
| 6 | UNION-ALL | | 1 | | 2634 |00:00:00.05 | 954 | | | |
|* 7 | HASH JOIN | | 1 | 2010 | 1741 |00:00:00.03 | 479 | 1452K| 1452K| 1667K (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.02 | 475 | 1452K| 1452K| 1571K (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')
40 rows selected.
DB笔试面试历史连接
http://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：230161599
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
