表连接方式 - FILTER - 《SQL优化入门》

如果子查询（in/exists/not in/not exists）没能展开（unnest），在执行计划中就会产生 FILTER，FILTER 类似嵌套循环，FILTER 的算法与标量子查询一模一样。

现有如下 SQL 以及其执行计划。

 select ename, deptno
    from emp
   where exists (select deptno
            from dept
           where emp.deptno = dept.deptno
            and dname = 'RESEARCH'
             and rownum = 1);

Execution Plan
----------------------------------------------------------
Plan hash value: 3414630506
-------------------------------------------------------------------------------------
| Id | Operation                     | Name    | Rows  | Bytes | Cost(%CPU)|Time    |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |         |     5 |    45 |    6   (0)|00:00:01|
|* 1 |  FILTER                       |         |       |       |           |        |
|  2 |   TABLE ACCESS FULL           | EMP     |    14 |   126 |    3   (0)|00:00:01|
|* 3 |   COUNT STOPKEY               |         |       |       |           |        |
|* 4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |    1   (0)|00:00:01|
|* 5 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |    0   (0)|00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "DEPT" "DEPT" WHERE ROWNUM=1 AND
              "DEPT"."DEPTNO"=:B1 AND "DNAME"='RESEARCH'))
   3 - filter(ROWNUM=1)
   4 - filter("DNAME"='RESEARCH')
   5 - access("DEPT"."DEPTNO"=:B1)

执行计划中，Id=1 就是 FILTER。注意观察 FILTER 所对应的谓词信息，FILTER 对应的谓词中包含有 EXISTS（子查询:B1）。运用光标移动大法我们可以知道 FILTER 下面有两个儿子（Id=2，Id=3）。

现在我们来看一下上面 SQL 带有 A-Time 的执行计划。

alter session set statistics_level=all;

Session altered.

select ename, deptno
    from emp
   where exists (select deptno
             from dept
          where emp.deptno = dept.deptno
             and dname = 'RESEARCH'
             and rownum = 1);

ENAME                              DEPTNO
------------------------------ ----------
SMITH                                  20
JONES                                  20
SCOTT                                  20
ADAMS                                  20
FORD                                   20

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  6mq67by27udgm, child number 1
-------------------------------------
select ename, deptno   from emp  where exists (select deptno
from dept          where emp.deptno = dept.deptno            and dname
= 'RESEARCH'            and rownum = 1)
Plan hash value: 3414630506
----------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      5 |
|*  1 |  FILTER                       |         |      1 |        |      5 |
|   2 |   TABLE ACCESS FULL           | EMP     |      1 |     14 |     14 |
|*  3 |   COUNT STOPKEY               |         |      3 |        |      1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      1 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |      3 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   3 - filter(ROWNUM=1)
   4 - filter("DNAME"='RESEARCH')
   5 - access("DEPT"."DEPTNO"=:B1)

为了方便排版，执行计划中省略了部分内容。Id=2 以及 Id=3 都是 FILTER 的儿子。Id=2 靠近 FILTER，我们可以把 Id=2 理解为 FILTER 的驱动表；Id=3 离 FILTER 比较远，可以把 Id=3 理解为 FILTER 的被驱动表。驱动表 EMP 只扫描了一次（Id=2，Starts=1），被驱动表被扫描了 3 次（Id=3，Starts=3）。

FILTER 的算法与标量子查询一模一样，驱动表都是固定的（固定为主表），不可更改。

从执行计划中我们可以看到，主表（EMP）通过连接列（DEPTNO）传值给子表（DEPT），:B1 就表示传值，主表（EMP）的连接列（DEPTNO）基数为 3，所以被驱动表（DEPT）被扫描了 3 次。FILTER 一般在整个 SQL 的快要执行完毕的时候执行（Filter 的 Id 一般小于等于 3）。

请注意，执行计划中还有一种 FILTER，这类 FILTER 只起过滤作用，这类 FILTER 下面只有一个儿子，谓词中没有 exists，也没有绑定变量:B1，例子如下。

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
| Id | Operation                       |  Name                | Rows  | Bytes |Cost |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |                      |     1 |    81 | 1618|
|  1 |  SORT AGGREGATE                 |                      |     1 |    81 |     |
|* 2 |   FILTER                        |                      |       |       |     |
|* 3 |    HASH JOIN OUTER              |                      |       |       |     |
|  4 |     NESTED LOOPS OUTER          |                      |   642 | 38520 |  838|
|* 5 |      INDEX FAST FULL SCAN       | PK_T_SEND_VEHICLE    |   413 |  8260 |   12|
|  6 |      TABLE ACCESS BY INDEX ROWID| T_TASK_HEAD          |     2 |    80 |    2|
|* 7 |       INDEX RANGE SCAN          | IDX_TASK_VEHICLE_NO  |     2 |       |    1|
|  8 |     TABLE ACCESS FULL           | T_TASK_DETAIL        |   162K|  3337K|  777|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TTASKDETAI2_"."IS_REAL"='N' OR "TTASKDETAI2_"."IS_REAL" IS NULL)
   3 - access("TRANSTASKH0_"."TRANS_TASK_NO"="TTASKDETAI2_"."TRANS_TASK_NO"(+))
   5 - filter(TRIM("SENDVEHICL1_"."SEND_VEHICLE_NO")='01037041212280054')
   7 - access("TRANSTASKH0_"."SEND_VEHICLE_NO"(+)="SENDVEHICL1_"."SEND_VEHICLE_NO")

我们在做 SQL 优化的时候，一般只需要关注 FILTER 下面有两个或者两个以上儿子这种 FILTER。关于如何避免执行计划中产生 FILTER 以及执行计划中产生了 FILTER 怎么优化，请参阅本书 7.1 节。