【DB笔试面试590】在Oracle中，什么是反连接（Anti Join）？

2022-09-23 09:29:21

♣ 题目部分

在Oracle中，什么是反连接（Anti Join）？

♣ 答案部分

反连接（Anti Join）也是一种特殊的连接类型，通常用于从一个表中返回不在另一个数据源中的数据行。当做子查询展开时，Oracle经常会把那些外部WHERE条件为NOT EXISTS、NOT IN或<> ALL的子查询转换成对应的反连接。反连接分为嵌套循环反连接（NESTED LOOPS ANTI，Hint为：NL_AJ）、排序合并反连接（MERGE JOIN ANTI，Hint为：MERGE_AJ）和哈希反连接（HASH JOIN ANTI，Hint为：HASH_AJ）。示例如下所示：

 1CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
 2CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
 3SELECT * FROM EMP A WHERE NOT EXISTS(SELECT 1 FROM DEPT WHERE DEPTNO=A.DEPTNO);
 4---------------------------------------------------------------------------
 5| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 6---------------------------------------------------------------------------
 7|   0 | SELECT STATEMENT   |      |     2 |    84 |     5  (20)| 00:00:01 |
 8|*  1 |  HASH JOIN ANTI    |      |     2 |    84 |     5  (20)| 00:00:01 |
 9|   2 |   TABLE ACCESS FULL| EMP  |    12 |   468 |     2   (0)| 00:00:01 |
10|   3 |   TABLE ACCESS FULL| DEPT |     4 |    12 |     2   (0)| 00:00:01 |
11---------------------------------------------------------------------------
12SELECT * FROM EMP A WHERE NOT EXISTS(SELECT /*+NL_AJ*/ 1 FROM DEPT WHERE DEPTNO=A.DEPTNO);
13---------------------------------------------------------------------------
14| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
15---------------------------------------------------------------------------
16|   0 | SELECT STATEMENT   |      |     2 |    84 |     7   (0)| 00:00:01 |
17|   1 |  NESTED LOOPS ANTI |      |     2 |    84 |     7   (0)| 00:00:01 |
18|   2 |   TABLE ACCESS FULL| EMP  |    12 |   468 |     2   (0)| 00:00:01 |
19|*  3 |   TABLE ACCESS FULL| DEPT |     4 |    12 |     0   (0)| 00:00:01 |
20---------------------------------------------------------------------------
21SELECT * FROM EMP A WHERE NOT EXISTS(SELECT /*+MERGE_AJ*/ 1 FROM DEPT WHERE DEPTNO=A.DEPTNO);
22----------------------------------------------------------------------------
23| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
24----------------------------------------------------------------------------
25|   0 | SELECT STATEMENT    |      |     2 |    84 |     6  (34)| 00:00:01 |
26|   1 |  MERGE JOIN ANTI    |      |     2 |    84 |     6  (34)| 00:00:01 |
27|   2 |   SORT JOIN         |      |    12 |   468 |     3  (34)| 00:00:01 |
28|   3 |    TABLE ACCESS FULL| EMP  |    12 |   468 |     2   (0)| 00:00:01 |
29|*  4 |   SORT UNIQUE       |      |     4 |    12 |     3  (34)| 00:00:01 |
30|   5 |    TABLE ACCESS FULL| DEPT |     4 |    12 |     2   (0)| 00:00:01 |
31----------------------------------------------------------------------------

需要注意的是，NOT IN和<> ALL对NULL值敏感，这意味着NOT IN后面的子查询或者常量集合一旦有NULL值出现，则整个SQL的执行结果就会为NULL，即此时的执行结果将不包含任何记录。但是，NOT EXISTS对NULL值不敏感，这意味着NULL值对NOT EXISTS的执行结果不会有什么影响。正是因为NOT IN和<> ALL对NULL值敏感，所以一旦相关的连接列上出现了NULL值，此时Oracle如果还按照通常的反连接的处理逻辑来处理，得到的结果就不对了。为了解决NOT IN和<> ALL对NULL值敏感的问题，Oracle推出了改良的反连接，这种反连接能够处理NULL值，Oracle称其为Null-Aware Anti Join，如下例：

1SELECT * FROM DEPT A WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
2---------------------------------------------------------------------------
3| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
4---------------------------------------------------------------------------
5|   0 | SELECT STATEMENT   |      |     1 |    23 |     5  (20)| 00:00:01 |
6|*  1 |  HASH JOIN ANTI NA |      |     1 |    23 |     5  (20)| 00:00:01 |
7|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     2   (0)| 00:00:01 |
8|   3 |   TABLE ACCESS FULL| EMP  |    12 |    36 |     2   (0)| 00:00:01 |
9---------------------------------------------------------------------------

执行步骤的列Operation的值为“HASH JOIN ANTI NA”，关键字“NA”就是Null-Aware的缩写，表示这里采用的不是普通的哈希反连接，而是改良后的、能够处理NULL值的哈希反连接。

在Oracle 11gR2中，Oracle是否启用Null-Aware Anti Join受隐含参数“_OPTIMIZER_NULL_AWARE_ANTIJOIN”控制，其默认值为TRUE，表示启用Null-Aware Anti Join。如果把该参数的值修改为FALSE，那么表示Oracle就不能再用Null-Aware Anti Join了，而又因为NOT IN对NULL值敏感，所以Oracle此时也不能用普通的反连接。关于该隐含参数的查询如下所示：

 1SYS@orclasm > set pagesize 9999
 2SYS@orclasm > set line 9999
 3SYS@orclasm > col NAME format a40
 4SYS@orclasm > col KSPPDESC format a50
 5SYS@orclasm > col KSPPSTVL format a20
 6SYS@orclasm > SELECT a.INDX,
 7  2         a.KSPPINM NAME,
 8  3         a.KSPPDESC,
 9  4         b.KSPPSTVL 
10  5  FROM   x$ksppi  a,
11  6         x$ksppcv b
12  7  WHERE  a.INDX = b.INDX
13  8  and lower(a.KSPPINM) like  lower('%&parameter%');
14Enter value for parameter: _OPTIMIZER_NULL_AWARE_ANTIJOIN
15old   8: and lower(a.KSPPINM) like  lower('%&parameter%')
16new   8: and lower(a.KSPPINM) like  lower('%_OPTIMIZER_NULL_AWARE_ANTIJOIN%')
17
18      INDX NAME                                     KSPPDESC                                           KSPPSTVL
19---------- ---------------------------------------- -------------------------------------------------- --------------------
20      1907 _optimizer_null_aware_antijoin           null-aware antijoin parameter                      TRUE