【DB笔试面试590】在Oracle中，什么是反连接（Anti Join）？
【DB笔试面试590】在Oracle中，什么是反连接（Anti Join）？
♣
题目部分
在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）。示例如下所示：
CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
SELECT * FROM EMP A WHERE NOT EXISTS(SELECT 1 FROM DEPT WHERE DEPTNO=A.DEPTNO);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 84 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 2 | 84 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 12 | 468 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
SELECT * FROM EMP A WHERE NOT EXISTS(SELECT /*+NL_AJ*/ 1 FROM DEPT WHERE DEPTNO=A.DEPTNO);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 84 | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 2 | 84 | 7 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 12 | 468 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------
SELECT * FROM EMP A WHERE NOT EXISTS(SELECT /*+MERGE_AJ*/ 1 FROM DEPT WHERE DEPTNO=A.DEPTNO);
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 84 | 6 (34)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 2 | 84 | 6 (34)| 00:00:01 |
| 2 | SORT JOIN | | 12 | 468 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 12 | 468 | 2 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 4 | 12 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT | 4 | 12 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
需要注意的是，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，如下例：
SELECT * FROM DEPT A WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 1 | 23 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 12 | 36 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
执行步骤的列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此时也不能用普通的反连接。关于该隐含参数的查询如下所示：
SYS@orclasm > set pagesize 9999
SYS@orclasm > set line 9999
SYS@orclasm > col NAME format a40
SYS@orclasm > col KSPPDESC format a50
SYS@orclasm > col KSPPSTVL format a20
SYS@orclasm > SELECT a.INDX,
2 a.KSPPINM NAME,
3 a.KSPPDESC,
4 b.KSPPSTVL
5 FROM x$ksppi a,
6 x$ksppcv b
7 WHERE a.INDX = b.INDX
8 and lower(a.KSPPINM) like lower('%¶meter%');
Enter value for parameter: _OPTIMIZER_NULL_AWARE_ANTIJOIN
old 8: and lower(a.KSPPINM) like lower('%¶meter%')
new 8: and lower(a.KSPPINM) like lower('%_OPTIMIZER_NULL_AWARE_ANTIJOIN%')
INDX NAME KSPPDESC KSPPSTVL
---------- ---------------------------------------- -------------------------------------------------- --------------------
1907 _optimizer_null_aware_antijoin null-aware antijoin parameter TRUE
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。