编者按：
本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页:https://blog.csdn.net/weixin_50513167，经其本人授权发布。
【免责声明】本号文章仅代表个人观点，与任何公司无关。
其实上一篇文章的初衷是为了捋顺一下 null aware anti-join 机能做的一个铺垫。为什么专门重点来说 Null aware(NA) 机能呢，是因为工作中可能会遇到过这个机能造成的 BUG 还是比较多，所以，想做个单独总结。
反结合的NULL识别机能（null aware anti-join ）
前面的文章已经说过了子查询展开机能，这个机能在有些时候是没法使用的，比如 NOT IN 子句中坑包含 NULL 。假如，NOT IN 子句中成员中有 NULL 的话就相当于 !=ALL。
那这种情况下，CBO 如何来转换用户的 SQL 呢。11g 开始 Oracle 为我们提供了 null aware anti-join 机能，我们再来看看这个机能长什么样子。
首先还是看看在没有这个机能之前 SQL 是怎么执行的。
稍微修改一下前面文章的测试 case 中表的字段定义，将 C2 列 not null 的限制去掉。
代码语言：javascript复制drop table t1 purge;
drop table t2 purge;
create table t1(c1 number, c2 number);
create table t2(c1 number primary key, c2 number);
insert into t1 values (1,null);
insert into t1 values (1,1);
insert into t1 values (1,2);
insert into t2 values (1,2);
commit;
-- 无效 null aware anti-join 机能
SQL> alter session set "_optimizer_null_aware_antijoin"=FALSE;
SQL> select t1.* from t1 where c2 not in (select c2 from t2);
C1 C2
---------- ----------
1 1
Execution Plan
----------------------------------------------------------
Plan hash value: 895956251
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 3 | 78 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE
LNNVL("C2"<>:B1)))
3 - filter(LNNVL("C2"<>:B1))
SQL> select t1.* from t1 where c2 not in (select c2 from t2 where c2 is not null);
C1 C2
---------- ----------
1 1
Execution Plan
----------------------------------------------------------
Plan hash value: 895956251
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 3 | 78 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE "C2" IS NOT
NULL AND LNNVL("C2"<>:B1)))
3 - filter("C2" IS NOT NULL AND LNNVL("C2"<>:B1))因为 C2 列本身是可以是 NULL ，所以，ANTI 结合没有用上，SQL 没有经过转换。
如果使用 null aware anti-join 机能后呢，就是下面的样子了。
代码语言：javascript复制-- 有效 null aware anti-join 机能
SQL> alter session set "_optimizer_null_aware_antijoin"=true;
SQL> select t1.* from t1 where c2 not in (select c2 from t2);
C1 C2
---------- ----------
1 1
Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 3 | 117 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 78 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C2"="C2")
SQL> select t1.* from t1 where c2 not in (select c2 from t2 where c2 is not null);
C1 C2
---------- ----------
1 1
Execution Plan
----------------------------------------------------------
Plan hash value: 1270581391
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI SNA| | 3 | 117 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 78 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C2"="C2")
3 - filter("C2" IS NOT NULL)完美！！SQL 转换了，子查询展开机能用到了，T1 和 T2 进行了 ANTI 结合，同时，也进行了 NULL 识别 Null-Aware(NA) 或者 Single Null-Aware(SNA) 。
当然，我们在写 SQL 的时候假如能把带有 NULL 的可能性给排除掉的话，我认为是最理想的，可以避免很多不必要的麻烦，这就要求各位程序员同学们编写 SQL 时需要注意到一些细节，不要过分指望 Oracle 的优化器来排除全部问题。不知道各位怎么认为。
代码语言：javascript复制SQL> select t1.* from t1 where c2 is not null and c2 not in (select c2 from t2 where c2 is not null);
C1 C2
---------- ----------
1 1
Execution Plan
----------------------------------------------------------
Plan hash value: 2706079091
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 39 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 2 | 52 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C2"="C2")
2 - filter("C2" IS NOT NULL)
3 - filter("C2" IS NOT NULL)null aware anti-join 的关闭方法，上面的例子中已经用到，就不再赘述了。
