기본 메커니즘
- Build 단계 : 작은 쪽 테이블(Build Input)을 읽어 해시 테이블(해시 맵)을 생성한다
- Probe 단계 : 큰 쪽 테이블 (Probe Input)을 읽어 해시 테이블을 탐색하면서 조인한다
- use_hash : 해시 조인 유도
< 원본 쿼리 >
select /*+ ordered use_hash(c) */
e.사원번호, e.사원명, e.입사일자,
c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.관리사원번호 = e.관리사원번호
and e.입사일자 >= '19960101'
and e.부서코드 = 'Z123'
and c.최종주문금액 >= 2000↓ 수행과정 풀이
1) Build 단계
- 아래의 조건에 해당하는 사원 데이터를 읽어 해시 테이블을 생성한다.
조인 컬럼인 사원번호를 해시 테이블 키 값으로 사용한다.
사원번호를 해시 함수에 입력해서 반환값으로 해시 체인을 찾고
그 해시 체인에 데이터를 연결한다.
해시 테이블은 PGA영역에 할당된 Hash Area에 저장한다(너무 크면 temp 테이블스페이스)
select 사원번호, 사원명, 입사일자
from 사원
where 입사일자 >= '19960101'
and 부서코드 = 'Z123'2) Probe 단계
- 아래 조건에 해당하는 고객 데이터를 하나씩 읽어 앞서 생성한 해시 테이블을 탐색한다
관리사원번호를 해시 함수에 입력해서 반환된 값으로 해시 체인을 찾고
그 해시 체인을 스캔해서 값이 같은 사원번호를 찾는다
찾으면 성공 , 못찾으면 실패
select 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
from 고객
where 최종주문금액 >= 20003) Probe 단계에서 조인하는 과정
(조인하는 과정을 PL/SQL로 표현)
begin
for outer in (select 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
from 고객 where 최종주문금액 >= 20000)
loop -- outer 루프
for inner in (select 사원번호, 사원명, 입사일자
from PGA에_생성한_사원_해시맵
where 사원번호 = outer.관리사원번호)
loop -- inner 루프
dbms_output.put_line( ... );
end loop;
end loop;
end;➕ 해시테이블에는 조인 키 값뿐만 아니라 SQL에 사용한 컬럼을 모두 저장한다.
소트 머지 조인 보다 해시 조인이 빠른 이유
- 사전 준비 작업에서 차이가 발생
- 소트 머지 조인은 양쪽 집합을 모두 정렬해서 PGA에 담는데, PGA는 그리 큰 메모리 공간이 아니므로 두 집합 중 어느하나가 중대형 이상이면 Temp 테이블스페이스(디스크)에 쓰는 작업을 반드시 수반한다.
- 해시 조인은 양쪽 집합 중 어느 한쪽을 읽어서 해시 맵을 만드는 작업을 진행하며, 둘 중 작은 집합을 Build Input으로 선택하므로 두 집합 모두 Hash Area에 담을 수 없는 정도로 큰 경우가 아니면 디스크에 쓰는 작업은 일어나지 않는다.
해시 조인은 NL조인처럼 조인 과정에서 발생하는 랜덤 액세스 부하가 없고, 소트 머지 조인처럼 양쪽 집합을 미리 정렬하는 부하도 없다.
해시 테이블을 생성하는 비용이 수반되지만, 둘 중 작은 집합을 Build Input으로 선택하므로 대개는 부담이 크지 않다.
( Temp 테이블스페이스를 쓰게 되더라도 대량 데이터 조인할 때는 일반적으로 해시 조인이 가장 빠르다 )
➕ 인메모리 해시 조인 : Build Input이 PGA에 담길 때
대용량 Build Input 처리
조인 할 두 테이블 모두 대용량 테이블이어서 인메모리 해시조인이 불가능한 경우
⇒ 분할 · 정복 ( Divide & Conquer ) 방식으로 진행
① 파티션 단계
- 조인하는 양쪽 집합 ( = 조인 이외의 조건절을 만족하는 레코드 )의 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝 한다.
- 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합으로 분할함으로써 파티션(pair)을 생성하는 단계
- 양쪽 집합을 읽어 디스크 Temp 공간에 저장해야 하므로 인메모리 해시 조인보다 성능이 많이 떨어진다.
② 조인 단계
- 파티션 단계 완료 시 각 파티션 짝(pair)에 대해 하나씩 조인을 수행하며 이때, 각각에 대한 Build Input과 Probe Input은 독립적으로 결정된다.
- 파티션하기 전 어느 쪽이 작은 테이블이었는지에 상관없이 각 파티션 짝 별로 작은 쪽을 Build Input 으로 선택하고 해시 테이블을 생성한다.
- 해시 테이블을 생성하고 나면 반대쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색하며, 모든 파티션 짝에 대한 처리를 마칠 때까지 이 과정을 반복한다.
해시 조인 실행계획 제어
- use_hash 힌트만 사용하는 경우, Build Input을 옵티마이저가 선택함
- Build Input을 직접 선택하고 싶은 경우 ex) buld input : e / probe input: c
- /*+ leading(e) use_hash(c) */
- /*+ ordered use_hash(c) */
- /*+ leading(e) use_hash(c) swap_join_inputs(c) */
조인 메서드 선택 기준
일반적인 조인 메서드 기준
- 소량 데이터 조인할 때 → NL 조인
- 대량 데이터 조인할 때 → 해시 조인
- 대량 데이터 조인인데 해시 조인으로 처리할 수 없을 때(= 조인 조건식이 등치 조건이 아닐때, 카테시안 곱 포함) → 소트 머지 조인
➕ 소량과 대량의 기준
단순히 데이터의 많고 적음이 아니라, NL 조인 기준으로 최적화했는데도 랜덤 액세스가 많이 만족할만한 성능을 낼 수 없다면 대량 데이터 조인에 해당.
수행빈도가 매우 높은 쿼리
- (최적화된) NL 조인과 해시 조인 성능이 같으면 → NL 조인
- 해시 조인보다 약간 더 빠름 → NL 조인
- NL 조인보다 해시 조인이 매우 빠른 경우(= 대량 데이터 조인) → 해시 조인
NL 조인을 가장 먼저 고려해야 하는 이유
- NL 조인에 사용되는 인덱스는 DBA가 Drop하지 않는 한 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 구조
- 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조
- 수행시간이 짧으면서 수행빈도가 매우 높은 쿼리를 해시 조인으로 처리하면 CPU와 메모리 사용률이 크게 증가하며, 해시 맵을 만드는 과정에서 여러 가지 래치 경합도 발생한다.
- 해시 조인을 만족하는 3가지 조건
- 수행 빈도가 낮고
- 쿼리 수행 시간이 오래 걸리는
- 대량 데이터 조인할 때
