NULL-aware Anti join

特性描述
NULL-aware Anti join是尝试解决子查询反嵌套（Subquery Unnesting）中和NULl值相关的各种问题，常见于not in/exists。
适用于主要的三种连接方式：sort-merge join, hash join 和 nested loops join
在执行计划中主要有两种形式：ANTI NA和ANTI SNA

特性开启/关闭
alter session set "_optimizer_null_aware_antijoin" = true;
alter session set "_optimizer_null_aware_antijoin" = false;

为了更清楚的理解NULL-aware Anti join，特进行如下实验
SQL> desc scott.emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> alter session set "_optimizer_null_aware_antijoin" = true;  -- 开启NULL-aware Anti join特性

-- 两边都包含NULL值
SQL> select * from scott.emp where mgr not in (select mgr from scott.emp);

Execution Plan
----------------------------------------------------------
Plan hash value: 4002838083

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    84 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     2 |    84 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MGR"="MGR")


-- 外层查询包含NULL值   
SQL> select * from scott.emp where mgr not in (select mgr from scott.emp where mgr is not null);

Execution Plan
----------------------------------------------------------
Plan hash value: 902005141

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    84 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI SNA|      |     2 |    84 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    13 |    52 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MGR"="MGR")
   3 - filter("MGR" IS NOT NULL)

-- 子查询包含NULL值   
SQL> select * from scott.emp where mgr not in (select mgr from scott.emp) and mgr is not null;


Execution Plan
----------------------------------------------------------
Plan hash value: 4002838083


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    42 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     1 |    42 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |    13 |   494 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("MGR"="MGR")
   2 - filter("MGR" IS NOT NULL)


-- 两边都不包含NULL值

SQL> select * from scott.emp where mgr not in (select mgr from scott.emp where mgr is not null) and mgr is not null;

Execution Plan
----------------------------------------------------------
Plan hash value: 3353202012


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    42 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     1 |    42 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |    13 |   494 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    13 |    52 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MGR"="MGR")
   2 - filter("MGR" IS NOT NULL)
   3 - filter("MGR" IS NOT NULL)   
   
SQL> alter session set "_optimizer_null_aware_antijoin" = false; -- 关闭NULL-aware Anti join特性   

Session altered.

SQL> select * from scott.emp where mgr not in (select mgr from scott.emp);

Execution Plan
----------------------------------------------------------
Plan hash value: 2561671593

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    12 |   456 |    21   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     2 |     8 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT 0 FROM "SCOTT"."EMP" "EMP" WHERE
              LNNVL("MGR"<>:B1)))
   3 - filter(LNNVL("MGR"<>:B1))


SQL> select * from scott.emp where mgr not in (select mgr from scott.emp where mgr is not null) and mgr is not null;

Execution Plan
----------------------------------------------------------
Plan hash value: 3353202012

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    42 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     1 |    42 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |    13 |   494 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    13 |    52 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MGR"="MGR")
   2 - filter("MGR" IS NOT NULL)
   3 - filter("MGR" IS NOT NULL)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29843794/viewspace-1355432/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29843794/viewspace-1355432/