关于含FILTER操作的SQL执行计划的两类技术疑问

原始查询与执行计划 #

查询1 #

SELECT *
  FROM emp
 WHERE deptno IN (SELECT /*+ NO_UNNEST FULL(dept) */ deptno FROM dept WHERE deptno = 10);

---------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      3 |
|*  1 |  FILTER             |      |      1 |        |      3 |
|   2 |   TABLE ACCESS FULL | EMP  |      1 |     14 |     14 |
|*  3 |   FILTER            |      |      3 |        |      1 |
|*  4 |    TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( IS NOT NULL)
   3 - filter(10=:B1)
   4 - filter("DEPTNO"=:B1)

查询2 #

SELECT *
  FROM emp
 WHERE deptno IN (SELECT /*+ NO_UNNEST */ deptno FROM dept WHERE deptno = 10);

------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |      3 |
|*  1 |  FILTER             |         |      1 |        |      3 |
|   2 |   TABLE ACCESS FULL | EMP     |      1 |     14 |     14 |
|*  3 |   FILTER            |         |      3 |        |      1 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |      1 |      1 |      1 |
------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( IS NOT NULL)
   3 - filter(10=:B1)
   4 - access("DEPTNO"=:B1)

查询3 #

SELECT *
  FROM emp
 WHERE deptno IN (SELECT /*+ NO_UNNEST */ deptno FROM dept WHERE deptno = 10 OR deptno = 20);

------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |      8 |
|*  1 |  FILTER             |         |      1 |        |      8 |
|   2 |   TABLE ACCESS FULL | EMP     |      1 |     14 |     14 |
|*  3 |   FILTER            |         |      3 |        |      2 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |      2 |      1 |      2 |
------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( IS NOT NULL)
   3 - filter((10=:B1 OR 20=:B2))
   4 - access("DEPTNO"=:B1)
       filter(("DEPTNO"=10 OR "DEPTNO"=20))

问题 #

问题1 #

观察前两个查询的执行计划，其id=4步骤的A-Rows均为1。第一个查询将子查询的WHERE子句deptno = 10作为表过滤条件，第二个查询将该条件作为索引过滤条件。按此逻辑，filter("DEPTNO" = 10)应像第三个查询id=4步骤的filter("DEPTNO"=10 OR "DEPTNO"=20)一样显示，请问为何未如此呈现？

问题2 #

在前两个查询中，子查询条件deptno = 10已通过id=4步骤完成过滤，为何仍会出现id=3的FILTER操作？

解答 #

问题1解答 #

这是因为Oracle优化器处理单值等值条件和多值OR条件的逻辑有差异：

• 前两个查询的deptno=10是单值等值条件，优化器将该条件转化为绑定变量:B1传递给DEPT表/索引的访问逻辑，这个绑定变量直接匹配目标值，已经完全限定了查询结果，不需要在执行计划里重复显示原字面量的过滤条件。
• 第三个查询的deptno=10 OR deptno=20是多值OR条件，此时绑定变量:B1仅传递EMP表当前行的deptno值，子查询需要额外校验该值是否属于{10,20}集合，所以必须在执行计划里显式标注这个过滤逻辑，确保每个EMP的deptno都经过集合校验。

问题2解答 #

这是NO_UNNEST提示强制Oracle采用嵌套过滤驱动的执行模式导致的：

1. 先全表扫描EMP表（id=2），取出每条记录的deptno作为绑定变量:B1传给子查询。
2. id=3的FILTER是用来判断当前EMP的deptno是否能在DEPT表中找到匹配——也就是执行IN条件的核心校验逻辑：只有子查询返回非空结果，这条EMP记录才会被保留。
3. id=4的步骤是实际执行DEPT表/索引的访问操作，而id=3的FILTER是外层的结果判断开关，二者是不同层面的逻辑，前者负责取数据，后者负责判断是否保留当前EMP行，所以必须同时存在。

内容的提问来源于stack exchange，提问作者drj9812