CBO 查询转换(2):反结合的NULL识别机能（null aware anti-join ）
编者按：
本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页: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 的限制去掉。
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 1Execution 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" WHERELNNVL("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 1Execution 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 NOTNULL AND LNNVL("C2"<>:B1)))3 - filter("C2" IS NOT NULL AND LNNVL("C2"<>:B1))
因为 C2 列本身是可以是 NULL ，所以，ANTI 结合没有用上，SQL 没有经过转换。
如果使用 null aware anti-join 机能后呢，就是下面的样子了。
-- 有效 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 1Execution 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 1Execution 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 的优化器来排除全部问题。不知道各位怎么认为。
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 1Execution 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 的关闭方法，上面的例子中已经用到，就不再赘述了。
后续文章更加精彩，欢迎关注本公众号或访问【阅读原文】。
——End——
专注于技术不限于技术！
用碎片化的时间，一点一滴地提高数据库技术和个人能力。
欢迎关注！
数据库基础系列（从基础了解数据库）：
通过寄存服务来“理解”Oracle数据库基本体系结构和动作流程
手把手系列（帮助个人技术成长）：
CBO 查询转换系列（了解Oracle优化器）
原文作者：SQL和数据库技术；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
