Oracle 19C 反联接

原创 Asher.HU 2021-02-04

565

反联接是两个数据集之间的联接，当子查询数据集中不存在匹配的行时，该联接从第一组返回一行。

与半联接一样，当找到第一个匹配项时，反联接将停止处理子查询数据集。与半联接不同，反联接仅在找不到匹配项时才返回一行。

当优化器考虑防：联接时，当查询仅在不存在匹配项时返回一行时，防联接可避免不必要的处理。
反联接如何工作：根据所使用的联接类型，反联接优化的实现方式有所不同。
Antijoins如何处理空值：对于半联接，IN并且EXISTS在功能上等效。但是，由于存在null NOT IN，NOT EXISTS在功能上不等效。

9.3.4.1当优化器考虑反连接时

当查询仅需要在不存在匹配项时返回一行时，反联接可避免不必要的处理。

对于大型数据集，此优化可以通过嵌套循环联接节省大量时间。后者的联接必须遍历内部查询为外部查询中的每一行返回的每条记录。优化器可以将反联接优化应用于嵌套循环联接，哈希联接和排序合并联接。

在以下情况下，优化器可以选择一个反连接：

该语句使用NOT IN or NOT EXISTS子句。
该语句在NOT IN or NOT EXISTS子句中有一个子查询。
该NOT IN或NOT EXISTS条款不包含内部OR分支。

该语句执行外联接并将IS NULL条件应用于联接列，如以下示例所示：

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT emp.*
FROM   emp, dept
WHERE  emp.deptno = dept.deptno(+)
AND    dept.deptno IS NULL

Execution Plan
----------------------------------------------------------
Plan hash value: 1543991079
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |  1400 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |    14 |  1400 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |  1218 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)复制

9.3.4.2反联接如何工作

根据所使用的连接类型，反连接优化的实现方式有所不同。

以下伪代码显示了嵌套循环连接的反连接：

FOR ds1_row IN ds1 LOOP
  match := true;
  FOR ds2_row IN ds2 LOOP
    IF (ds1_row matches ds2_row) THEN
      match := false;
      EXIT -- stop processing second data set when a match is found
    END IF
  END LOOP
  IF (match = true) THEN 
    RETURN ds1_row
  END IF
END LOOP
复制

在前面的伪代码中，ds1是第一个数据集，并且ds2是第二个数据集。该代码从第一数据集获得第一行，然后循环遍历第二数据集以寻找匹配项。一旦找到匹配项，该代码将退出内部循环，并开始处理第一个数据集中的下一行。

示例9-15使用WHERE EXISTS的半联接

以下查询使用WHERE EXISTS子句仅列出包含员工的部门：

SELECT department_id, department_name 
FROM   departments
WHERE EXISTS (SELECT 1
              FROM   employees 
              WHERE  employees.department_id = departments.department_id)
复制

执行计划揭示了NESTED LOOPS SEMI步骤1中的操作：

--------------------------------------------------------------------------------
| Id| Operation          | Name              |Rows|Bytes |Cost(%CPU)| Time     |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |                   |    |      |   2 (100)|          |
| 1 |  NESTED LOOPS SEMI |                   | 11 |  209 |   2   (0)| 00:00:01 |
| 2 |   TABLE ACCESS FULL| DEPARTMENTS       | 27 |  432 |   2   (0)| 00:00:01 |
|*3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 44 |  132 |   0   (0)|          |
--------------------------------------------------------------------------------
复制

对于departments组成外部循环的中的每一行，数据库获取部门ID，然后探测employees.department_id索引以查找匹配的条目。从概念上讲，索引如下所示：

10,rowid
10,rowid
10,rowid
10,rowid
30,rowid
30,rowid
30,rowid
...
复制

如果departments表中的第一条记录是department 30，那么数据库将对索引执行范围扫描，直到找到第一个30条目为止，此时它将停止读取索引并从中返回匹配的行departments。如果外部循环的下一行是department 20，则数据库将扫描索引以20查找条目，并且未找到任何匹配项，则执行外部循环的下一次迭代。数据库以这种方式进行，直到返回所有匹配的行。

9.3.4.3反联接如何处理空值

对于半联接，IN并且EXISTS在功能上等效。但是，由于存在null NOT IN，NOT EXISTS在功能上不等效。

如果将空值返回给NOT IN运算符，则该语句不返回任何记录。要了解原因，请考虑以下WHERE子句：

WHERE department_id NOT IN (null, 10, 20)
复制

数据库按以下方式测试前面的表达式：

WHERE (department_id != null) 
AND   (department_id != 10) 
AND   (department_id != 20)
复制

要使整个表达式成为true条件，每个条件都必须为true。但是，不能将空值与另一个值进行比较，因此department_id !=null条件不能为true，因此整个表达式始终为false。通过以下技术，即使向NOT IN操作员返回空值，语句也可以返回记录：

将NVL函数应用于子查询返回的列。
将IS NOT NULL谓词添加到子查询。
实施NOT NULL约束。

与相比NOT IN，该NOT EXISTS子句仅考虑返回匹配项存在的谓词，并忽略由于空值而导致不匹配或无法确定的任何行。如果子查询中的至少一行与外部查询中的行匹配，则NOT EXISTS返回false。如果没有元组匹配，则NOT EXISTS 返回true。子查询中存在空值不会影响对匹配记录的搜索。

在 Oracle Database 11g 以前的版本中，当子查询可以返回空值时，优化器无法使用反联接优化。

但是，从Oracle Database 11g开始，以下各节中描述的ANTI NA（和ANTI SNA）优化使优化器即使在可能为null的情况下也可以使用反联接。

示例9-16使用NOT IN进行反连接

假设用户使用以下NOT IN子句发出以下查询，以列出不包含雇员的部门：

SELECT department_id, department_name
FROM   departments
WHERE  department_id NOT IN 
       (SELECT department_id 
        FROM   employees);
复制

即使多个部门没有员工，上述查询也不会返回任何行。由于employees.department_id列是可空的，因此发生了用户不希望的结果。

执行计划揭示了NESTED LOOPS ANTI SNA步骤2中的操作：

--------------------------------------------------------------------------------
| Id| Operation              | Name              |Rows|Bytes|Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |                   |    |     | 4(100)|          |
|*1 |  FILTER                |                   |    |     |       |          |
| 2 |   NESTED LOOPS ANTI SNA|                   | 17 | 323 | 4 (50)| 00:00:01 |  <--启用了null-aware join
| 3 |    TABLE ACCESS FULL   | DEPARTMENTS       | 27 | 432 | 2  (0)| 00:00:01 |
|*4 |    INDEX RANGE SCAN    | EMP_DEPARTMENT_IX | 41 | 123 | 0  (0)|          |
|*5 |   TABLE ACCESS FULL    | EMPLOYEES         |  1 |   3 | 2  (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NULL)
   4 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
   5 - filter("DEPARTMENT_ID" IS NULL)
复制

该ANTI SNA代表“单零感知反连接。” ANTI NA代表“空感知的反连接”。通过了解空值的操作，优化程序甚至可以在可为空的列上使用反联接优化。

假设用户通过将IS NOT NULL条件应用于子查询来重写查询：

SELECT department_id, department_name
FROM   departments
WHERE  department_id NOT IN 
       (SELECT department_id 
        FROM   employees
        WHERE  department_id IS NOT NULL);
复制

前面的查询返回16行，这是预期的结果。计划中的步骤1显示了标准NESTED LOOPS ANTI联接，而不是ANTI NAor ANTI SNA联接，因为子查询不能返回空值：

--------------------------------------------------------------------------------
|Id| Operation          | Name              | Rows| Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------------
| 0| SELECT STATEMENT   |                   |     |       |  2 (100)|          |
| 1|  NESTED LOOPS ANTI |                   |  17 |   323 |  2   (0)| 00:00:01 |
| 2|   TABLE ACCESS FULL| DEPARTMENTS       |  27 |   432 |  2   (0)| 00:00:01 |
|*3|   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |  41 |   123 |  0   (0)|          |
--------------------------------------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
       filter("DEPARTMENT_ID" IS NOT NULL)复制

示例9-17使用NOT EXISTS进行反连接

假设用户使用以下NOT EXISTS子句发出以下查询，以列出不包含雇员的部门：

SELECT department_id, department_name
FROM   departments d
WHERE  NOT EXISTS
       (SELECT null
        FROM   employees e
        WHERE  e.department_id = d.department_id)
复制

前面的查询避免了NOT IN子句的空问题。因此，即使employees.department_idcolumn可为空，该语句也会返回所需的结果。

执行计划的步骤1揭示了一个NESTED LOOPS ANTI操作，而不是ANTI NA变体，这是NOT IN可能出现null时所必需的：

--------------------------------------------------------------------------------
| Id| Operation          | Name              | Rows  | Bytes | Cost (%CPU)|Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |                   |       |       | 2 (100)|        |
| 1 |  NESTED LOOPS ANTI |                   |    17 |   323 | 2   (0)|00:00:01|
| 2 |   TABLE ACCESS FULL| DEPARTMENTS       |    27 |   432 | 2   (0)|00:00:01|
|*3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    41 |   123 | 0   (0)|        |
--------------------------------------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")复制

not in 和 <> all 对null 值敏感，这意味着not in 后面的子查询或常量集合，一旦有null 值出现，则SQL的执行结果就会为null ，即此时结果不包含任何记录

not exists 对 null 值不敏感，这意味着null 值对 not exists 的执行结果不会有影响。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C 反联接

9.3.4.1当优化器考虑反连接时

9.3.4.2反联接如何工作

9.3.4.3反联接如何处理空值

评论

相关阅读