­

【DB笔试面试258】在Oracle中，执行计划里的access和filter有什么区别（上）？

  • 2019 年 10 月 11 日
  • 筆記

题目如下所示：

在Oracle中，执行计划里的access和filter有什么区别？

答案如下所示：

如下所示：

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操作：

（一）单子节点：

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连用、复杂子查询等情况。

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'));

具体执行计划：

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

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

VirMach 便宜 VPS

QNews

QNews