CBO 查询转换(2):反结合的NULL识别机能（null aware anti-join ）

Oracle数据库技术 2021-03-27

468

编者按：

本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页: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          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 机能后呢，就是下面的样子了。

-- 有效 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 的优化器来排除全部问题。不知道各位怎么认为。

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)