이펙티브 오라클 (2008년)
조인에 대한 이해 0 0 657

by 구루비스터디 조인 [2009.12.02]


1. 중첩된 루프

  • 가장 일반적인 조인 테크닉


1.1 중첩된 루프를 이용한 자연 조인

sample 1)

select ename, dname
  from emp, dept
 where emp.deptno = dept.deptno;

  • 위의 쿼리는 결과적으로 아래와 처리방식이 동일함.


sample 2)

For x in (select * from emp)
Loop 
 Index lookup the ROWID for X.DEPTNO
 select * from dept where ROWID = that ROWID
 Output joined record
End loop

  • => 처리방식
    • 1. EMP테이블의 첫번째 데이터를 읽는다.
    • 2. 읽은 첫번째 데이터의 DEPTNO 값을 사용한다.
    • 3. 찾은 DEPTNO 값을 가지고 EMP테이블을 읽는다.(ename 가져옴)
    • 4. 참조된 블록을 DEPT 테이블에서 읽는다.


1.2 중첩된 루프를 이용한 외부 조인

sample 1)

select /*+ first_rows */ ename, dname
  from emp, dept
 where emp.deptno(+) = dept.deptno;



  • 자연 조인과 비교하여 조인 순서가 바뀌었음을 유의할 것. (직접 테스트 해본 결과 조인순서는 동일하였음. 10g)
  • 외부조인을 사용하면 최적화기가 사용할 수 있는 옵션이 제한되기 때문에 정말로 외부 조인이 필요한지 검토해 봐야함.(불필요한 외부조인은 사용하지 말 것)
  • => 중첩된 루프 조인은 결과의 마지막 집합의 행을 얻는데는 효율이 떨어진다. (부분범위 처리일 경우 매우 효과적임)


2. 해시 조인

중첩된 루프 조인과 해시 조인의 비교

중첩된 루프 조인


해시조인

  • 논리적인 I/O가 중첩된 루프 조인일 경우 36106 이던 것이 4022 밖에 사용되지 않음.
  • (책에서는 0.04%로 나왔으나 직접 테스트해 본 결과 11.14% 나옴)
  • 참고


2.1 해시 자연 조인

  • 두개의 테이블 중 작은 것을 취하여 메모리 내에 해시 테이블을 생성한다.(T1)
  • 해시함수에 적용된 조인키는 해시 테이블의 인덱스에 대응됨.
  • T2로 부터 OBJECT_ID값을 취하여 해시 함수에 적용암으로써 메모리 내에서 T1의 해당 행을 찾음.
  • 이런 해시 테이블은 전용 메모리에 존재하므로 일반적인 논리적인 I/O와 달리 래칭행위를 유발하지 않음.
  • 어차피 아무도 이들 행을 액세스 할 수 없기 때문에 래치를 이용하여 보호할 필요가 없음.
  • 첫번째 행을 얻는데는 시간이 걸리지만 모든행을 얻는데에는 효과적임.
  • 해시테이블이 메모리에 모두 수용되지 않을 경우에는 TEMP 공간을 사용한다.


2.2 해시 외부 조인

  • 행이 보존되지 않는 테이블 => 해시 함수를 적용.
  • 행이 보존되는 테이블 => 해시 테이블 생성.
  • 해시 조인은 두개의 큰 집합을 조인하거나 작은 집합을 큰 집합과 조인하는데 매우 뛰어나다.
  • 첫번째 행을 얻기 까지는 시간이 소요되지만 나머지 행을 얻는데는 효율이 매우 좋다.
  • (메모리 혹은 메모리와 디스크에 적재하기 때문)


3. 정렬 병합 조인

  • 각 테이블을 정렬한 후 결과를 병합하는 조인.
  • 두 입력 집합을 모두 스캔 한 후 정렬을 하여야 하기 때문에 해시조인보다 효율적이지 못함. (해시조인은 하나의 입력집합만 처리한 후 데이터가 출력되기 시작함)
  • 일반적으로 비동등조인작업(범위비교)에 유용하다.

정렬 병합 조인과 중첩된 루프 조인과의 비교

정렬 병합 조인


중첩된 루프 조인


해시조인

  • use_hash 힌트를 주었음에도 해시 조인을 하지 않고 nl 조인을 함을 볼 수 있음.
  • 참고
  • 정렬 병합 조인일 경우 논리적인 I/O가 14 이나 중첩된 루프 조인은 논리적인 I/O가 263으로 비동등 조인작업에서는 정렬 병합 조인이 효과적인 것을 볼 수 있음.
  • (비동등조인작업에서는 해시 조인을 할 수 없음)


4. 데카르트 조인 (Cartesion Product)

  • 조인되는 테이블 간의 연관성이 없는 경우 조인.
  • 조인되는 테이블들의 모든 행들 끼리 조인 된다. (A테이블 행수 * B테이블행수 = 결과 행수)
  • 실제로는 데카르트 조인이 포함되어 있지 않으나 경우에 따라서 데카르트 조인을 사용하도록 쿼리를 다시 쓰기도 함.


5. 반 조인 (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값을 인식해서 효과적으로 처리하겠다는 의미이다.
"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3563

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입