Anti Join

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/display/CORE/Anti+Join?
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

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값을 인식해서 효과적으로 처리하겠다는 의미이다.

문서에 대하여

최초작성자 : [김종원]
최초작성일 : 2009년 3월 21일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.

문서정보

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/display/CORE/Anti+Join?
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

Anti Join

Anti Join(역조인)

문서에 대하여

문서정보

Labels