Anti Join(역조인)

• 값이 동일하지 않은, 즉 존재하지 않는 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값을 인식해서 효과적으로 처리하겠다는 의미이다.