값이 동일하지 않은, 즉 존재하지 않는 Row를 탐색한다.
Not Exists 와 Not In Operation이 이런 역할을 한다.
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 not in (select t2.c1 from t2)
;
@stat
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | | 9000 |00:00:01.03 | 69483 | 36 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.06 | 638 | 30 |
|* 3 | TABLE ACCESS FULL| T2 | 10000 | 1 | 3 (0)| 1000 |00:00:00.96 | 68845 | 6 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - filter(LNNVL("T2"."C1"<>:B1))
Subquery Unesting이 이루어 지지 않는다. Filter Operation이 사용 됨.
왜? Anti Join은 NULL 값이 존재하지 않는다는 것이 보장될 때만 사용가능하다.
조건에 정확하게 IS NOT NULL을 부여하거나 Column 속성에 NOT NULL을 부여해야 한다.
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 not in (select t2.c1 from t2 where t2.c1 is not null) and
t1.c1 is not null
;
@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN RIGHT ANTI | | 1 | 9001 | 14 (8)| 9000 |00:00:00.07 | 645 | 1517K| 1517K| 1473K (0)|
|* 2 | INDEX FAST FULL SCAN| T2_N1 | 1 | 1000 | 2 (0)| 1000 |00:00:00.01 | 7 | | | |
|* 3 | TABLE ACCESS FULL | T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.05 | 638 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C1")
2 - filter("T2"."C1" IS NOT NULL)
3 - filter("T1"."C1" IS NOT NULL)
IS NOT NULL 조건이 부여된 경우 Hash Anti Join이 선택되었으며 일량이 645 Block으로 개선되었다.
Table Full Scan 대신 Index Fast Full Scan이 선택되었다.(B*Tree Index가 NULL값을 저장하지 않는다는 기본적인 전제 조건 때문이다.)
-- 11.1.0.6 에서 실행
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 not in (select t2.c1 from t2)
;
@stat
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN RIGHT ANTI NA| | 1 | 9000 | 15 (7)| 9000 |00:00:00.04 | 645 | 1517K| 1517K| 1514K (0)|
| 2 | TABLE ACCESS FULL | T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.03 | 638 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C1")
Oracle 11g의 Optimizer는 Null Aware Anti Join(ANTI NA)이라는 새로운 Join Operation을 추가했다.
말 그대로 Anti Join을 적용하되 NULL값을 인식해서 효과적으로 처리하겠다는 의미이다.