해시조인
소트 머지 조인과 해시 조인은 조인 과정에 인덱스를 이용하지 않기 때문에 대량 데이터 조인할 때 NL조인보다 훨씬 빠르고, 일정한 성능을 보인다.
NL 조인에 사용하는 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조다. 반면, 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조다.
결론적으로 해시조인은 다음 3가지 기준을 만족할 때 사용한다 (NL보다 조금 빠르다면 NL을 사용하라)
1. 수행 빈도가 낮고
2. 쿼리 수행 시간이 오래 걸리는
3. 대량 데이터 조인할 때
기본 메커니즘
1. Build 단계 : 작은 쪽 테이블을 읽어 해시 테이블을 생성한다.
조인 컬럼을 해시 테이블 키 값으로 사용한다. 해시 테이블은 PGA 영역에 할당된 Hash Area에 저장한다. 해시 테이블이 너무 커 PGA에 담을 수 없으면, Temp 테이블스페이스에 저장한다.
2. Probe 단계 : 큰 쪽 테이블을 읽어 해시 테이블을 탐색하면서 조인한다.
조건에 해당하는 큰쪽 테이블을 하나씩 읽어 앞서 생성한 해시 테이블을 탐색한다. 조인의 과정은 NL조인과 다르지 않다(중첩 For문 사용)
해시 조인이 빠른 이유
해시 조인이 인덱스 기반의 NL 조인보다 빠른 결정적인 이유는, 소트 머지 조인이 빠른 이유와 같다. 즉, 해시 테이블을 PGA영역에 할당하기 때문이다. NL 조인은 Outer 테이블 레코드마다 Inner쪽 테이블 레코드를 읽기 위해 래치 획득 및 캐시버퍼 체인 스캔 과정을 반복하지만, 해시 조인은 래치 획득 과정 없이 PGA에서 빠르게 데이터를 탐색하고 조인한다.
해시 조인도 Build Input과 Probe Input 각 테이블을 읽을 때는 DB 버퍼 캐시를 경유한다. 이때 인덱스를 이용하기도 한다. 이 과정에서 생기는 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 해시 조인이라도 피할 수 없다.
해시 테이블에 담기는 정보
해시 테이블에 조인 키값만 저장한다고 알고 있는 사람들이 많다. 그러나 해시 테이블에는
조인 키값뿐만 아니라 SQL에 사용한 컬럼을 모두 저장한다.
소트 머지 보다 빠른 이유
두 조인 메서드의 성능 차이는 조인 오퍼레이션을 시작하기 전, 사전 준비작업에 기인한다.
소트 머지 조인에서의 사전 작업은 양쪽 집합을 모두 정렬해서 PGA에 담는 작업니다
PGA는 큰 메모리 공간이 아니므로 두 집합 중 어느 하나가 중대형 이상이면, TEMP 테이블스페이스
즉 디스크에 쓰는 작업을 반드시 수반한다.
해시조인에서 사전 준비 작업은 양쪽 집합 중 어느 한쪽을 읽어 해시 맵을 만드는 작업이다.
해시조인은 둘 중 작은 집합을 해시 맵 Build Input으로 선택하므로 두 집합 모두 Hash Area에
담을 수 없을 정도로 큰 경우가 아니면, Temp 테이블스페이스, 즉 디스크에 쓰는 작업은 일어나지 않는다.
그렇다고 Build Input Hash Area 크기를 초과하면 다른 조인 메서드를 선택하라는 것은 아니다.
설령 Temp 테이블스페이스를 쓰게 되더라도 대량 데이터 조인할 때는 일반적으로 해시조인이 가장 빠르다.
대용량 Build Input 처리
두 테이블 모두 대용량 테이블이어서 인메모리 해시 조인이 불가능한 상황이다.
1.파티션 단계 : 조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝한다. 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝을 생성하는 단계이다. Temp 테이블스페이스에 저장해야 하므로 인메모리 해시 조인보다 성능이 많이 떨어진다.
2. 조인단계 : 파티션 짝별로 작은 쪽을 Build Input으로 선택하고 조인을 한다.
해시조인 실행계획 제어
힌트 : use_hash(a b) Build Input을 옵티마이저가 선택
leading(a) use_hash(b) 힌트를 지시한 순서에 따라 가정 먼저 읽는 테이블을 Build Input으로 선택
leading(a) use_hash(b) swap_join_inputs(b) Build Input을 b테이블로 명시적으로 선택
조인 메소드 선택 기준
1. 소량 데이터 조인 --> NL조인
2. 대량 데이터 조인 --> 해시조인
3. 대량 데이터 조인인데 해시 조인으로 처리할 수 없을 때, 즉 조인 조건식이 등치 조건이 아닐 때 --> 소트 머지 조인
수행 빈도가 높은 쿼리에 대해선 아래와 같은 기준을 제시한다.
1. NL조인과 해시조인의 성능이 같으면, NL조인
2. 해시조인이 약간 더 빨라도 NL조인
3. NL조인보다 해시조인이 매우 빠른 경우, 해시조인
그 이유는 단 하나의 쿼리를 위해 해시테이블을 생성하고 조인이 끝나면 곧바로 소멸하는 구조로, 같은 쿼리를 100개 프로세스가 동시에 수행하면, 해시 테이블도 100개 만들어진다. 따라서 CPU와 메모리 사용률이 크게 증가한다.
따라서 해시 조인은 아래 세 가지 조건을 만족하는 SQL문에 주로 사용한다.
1. 수행 빈도가 낮고
2. 쿼리 수행 시간이 오래 걸리고
3. 대량 데이터 조인