조인의 종류
- NL 조인(Nested Loop Join)
- 소트 머지 조인(Sort Merge Join)
- 해시 조인(Hash Join)
NL 조인
두 테이블을 조인할 때, 드라이빙 테이블에서 읽은 결과를 Inner 테이블로 건건이 조인을 시도하는 방식이다.
SELECT /*+ ordered use_nl(e) */
*
FROM dept d
, emp e
WHERE 1 = 1
AND d.deptno = e.deptno
dept 테이블에서 한 건 꺼내어 emp 테이블과 조인을 시도하여 성공된 것 만큼 결과에 담고, 다시 dept 테이블 다음건으로 emp 테이블을 조인한다.
다음 예시를 본다
SELECT /*+ ORDERED USE_NL(B) */
A.*
, B.*
FROM ITEM A
, UITEM B
WHERE A.ITEM_ID = B.ITEM_ID # 1
AND A.ITEM_TYPE_CD = '100100' # 2
AND A.SALE_YN = 'Y' # 3
AND B.SALE_YN = 'Y' # 4
인덱스는 다음 과 같다
- ITEM_X01 = ITEM_TYPE_CD
- UITEM_PK = ITEM_ID + UITEM_ID
우선 ITEM_TYPE_CD 인덱스를 통해 ‘100100’에 해당하는 테이블을 추출한다. 그 다음 SALE_YN이 ‘Y’로 최종 드라이빙 테이블을 만든다. 이 테이블로 UITEM 테이블과 조인 조건은 A.ITEM_ID = B.ITEM_ID를 수행하고 마지막으로 SALE_YN = ‘Y’에 맞는 조인을 수행한다.
실행순서 : 2 -> 3 -> 1 -> 4
소트 머지 조인
두 테이블을 각각 조건에 맞게 먼저 읽고, 읽은 두 테이블을 조인 칼럼을 기준으로 정렬해 놓고 조인을 수행한다. NL 조인은 드라이빙 테이블로 조인의 대상이 되는 테이블과 조인을 수행하였다면, 소트 머지 조인은 두 테이블을 우선 조건으로 추려내어 놓고 조인을 시도한다.
오라클은 정렬을 PGA라는 공간에서 수행한다. PGA 공간은 프로세스에 할당된 독립된 공간이므로 버퍼 캐시를 사용하는 NL 조인에 비해 데이터 접근이 더 빠르다.
SELECT /*+ ORDERED USE_MERGE(B) */
A.*
, B.*
FROM ITEM A
, UITEM B
WHERE A.ITEM_ID = B.ITEM_ID # 1
AND A.ITEM_TYPE_CD = '100101' # 2
AND A.SALE_YN = 'Y' # 3
AND B.SALE_YN = 'Y' # 4
인덱스는 다음과 같다
- ITEM_X01 -> ITEM_TYPE_CD
- UITEM -> 없음
쿼리 힌트대로 ITEM을 먼저 읽는다. ITEM 테이블의 ITEM_X01 인덱스를 이용해 A.ITEM_TYPE_CD = ‘100101’에 해당하는 데이터를 추출한다. 추출된 데이터를 가지고 테이블로 이동해 A.SALE_YN = ‘Y’에 해당하는 데이터를 최종 추출 데이터로 만든다. ITEM에서 최종적으로 추려진 데이터를 가지고 조인 컬럼인 A.ITEM_ID를 기준으로 정렬한다.
그 다음 UITEM 테이블을 읽는다. B.SALE_YN = ‘Y’에 해당하는 데이터를 최종 추출 데이터로 만들고 B.ITEM_ID로 정렬한다. 그리고 ITEM과 조인을 수행한다.
실행순서 : 2 -> 3 -> 4 -> 1
해시 조인
해시 알고리즘은 해시 맵을 만들 때, 해시 버킷을 생성하고 해시 함수를 통해 반환된 값으로 해시 버킷을 찾아가 해시 체인을 구성한다.
오라클에서 해시 조인은 해시 맵을 만들 때 두 테이블 중 작은 테이블을 읽어 만든다. 큰 테이블을 읽어 해시 함수를 통해 해시 버킷을 찾아가 실제 데이터를 찾는다. 여기서 작은 테이블을 Build Input이라 하고, 큰 테이블을 Probe Input이라고 한다.
해시 맵은 해시 함수를 사용해 해시 함수가 반환하는 값으로 해시 버킷을 찾아 해시 체인에 연결하면서 만든다. 해시 맵을 찾을때도 동일하다.
SELECT /*+ ORDERED USE_HASH(E) */
*
FROM DEPT D
, EMP E
WHERE 1 = 1
AND D.DEPTNO = E.DEPTNO
앞 SQL문을 보면 DEPT 테이블을 먼저 읽어 Build Imput으로 선택해 해시 맵을 만들고, EMP 테이블을 Probe Input으로 선택해 EMP 테이블을 읽으면서 조인을 시도한다.