by-nc-sa     개발자, 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 조인을 함을 볼 수 있음.

참고 http://cafe.naver.com/prodba.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=9958

=> 정렬 병합 조인일 경우 논리적인 I/O가 14 이나 중첩된 루프 조인은 논리적인 I/O가 263으로
비동등 조인작업에서는 정렬 병합 조인이 효과적인 것을 볼 수 있음.
(비동등조인작업에서는 해시 조인을 할 수 없음)

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

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

5. 반 조인 (Anti Join)

참고

6. 전체 외부 조인

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.