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

小麦苗DBA宝典

发布于 2019-09-30 16:20:25

1.2K0

发布于 2019-09-30 16:20:25

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

题目如下所示：

在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

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

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-01-18，如有侵权请联系 cloudcommunity@tencent.com 删除

access