꿈꾸는 개발자, DBA 커뮤니티 구루비

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