特性描述
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博客

NULL-aware Anti join

最新文章