4.3 해시 조인

• NL 조인은 인덱스를 이용한 조인으로 인덱스 구성에 따른 성능차이가 심하고, 랜덤I/O 로 인해 대량 데이터 처리에 불리하고, 버퍼캐시 히트율에 따라 성능이 들쭉날쭉하다.
• 소트 머지 조인과 해시 조인은 조인 과정에 인덱스를 사용하지 않아 대량 데이터 조인시 훨씬 빠르고 일정한 성능을 보인다.
• 소트 머지 조인은 양쪽 테이블을 정렬하는 부담이 있지만 해시 조인은 한쪽만 해시 테이블을 만들기 때문에 그런 부담이 없다.

4.3.1 기본 메커니즘

해시 조인(Hash Join)
1. Build 단계 : 작은 쪽 테이블(Build Input)을 읽어 해시 테이블(해시 맵)을 생성한다.
2. Probe 단계 : 큰 쪽 테이블(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인한다.

• 해시 조인은 use_hash 힌트로 유도한다.

-- use_hash : 해시 조인을 사용하라는 의미이다.
-- ordered를 명시하였으므로 먼저 읽은 사원 테이블을 기준으로 해시 맵을 생성
-- swap_join_inputs(e) 힌트를 사용하면 명시한 테이블 기준으로 해시 맵을 생성
SELECT /* ordered use_hash(c) */
	...
FROM 사원 e, 고객 c
WHERE c.관리자사원번호 = e.사원번호
AND e.입사일자 >= '19960101'
AND e.부서코드 >= 'Z123'
AND c.최종주문금액 >= 20000

1. Build 단계 : 작은 쪽 테이블(Build Input)을 기준으로 조인 컬럼을 Key로 하여 해시 맵을 생성 한다. 단, 아래는 ordered를 명시하였으므로 먼저 읽은 사원 테이블 기준으로 해시 맵을 생성 한다.

• 사원테이블을 읽어 해시 테이블을 생성한다,
• 이때 조인컬럼인 사원번호를 해시 테이블 키 값으로 사용한다.
• 사원 번호를 해시함수 키값으로 해시 체인을 찾고, 그 해시 체인에 데이터를 연결한다.
• 해시 테이블은 PGA 영역에 할당된 Hash Area 에 저장한다.
• 해시 테이블이 너무 큰 경우 Temp 테이블스페이스에 저장한다.

-- 사원 테이블을 읽어 해시 맵을 생성
-- 조인 컬럼인 사원번호를 해시 맵의 키 값으로 사용
SELECT
	...
FROM 사원
WHERE 입사일자 >= '19960101'
AND 부서코드 >= 'Z123'

2. Probe 단계 : 큰 쪽 테이블(Probe Input)을 읽어 조인 컬럼을 가지고 해시 맵을 탐색 하면서 조인

• 고객 데이터를 하나씩 읽어 앞에서 만든 해시 테이블을 탐색한다.
• 관리사원번호(조인컬럼)을 해시 함수에 입력해 반환된 값으로 해시 체인을 착고 스캔하여 같은 사원번호를 찾는다. 찾으면 조인 성공, 없으면 실패.

-- 고객 테이블을 하나씩 읽어 앞서 생성한 해시 맵을 탐색
-- 관리자사원번호를 해시 함수에 입력
SELECT
	...
FROM 고객
WHERE 최종주문금액 >= 20000

4.3.2 해시 조인이 빠른 이유

• 조인 방식은 NL 조인과 거의 동일하다.
• PGA 영역을 사용하고, 해시 조인은 래치 획득 과정이 없어 NL 보다 빠르다.
• 해시 조인도 Build Input 과 Probe Input 각 테이블을 읽을 때 DB 버퍼캐시를 경유하고 인덱스를 이용하기도 한다.

소트 머지 조인 vs 해시 조인

• 소트 머지 조인은 양쪽 집합을 정렬해야 한다.
  • 둘중 어느 하나가 중대형이라면 Temp 테이블스페이스, 즉 디스크에 쓰는 작업을 수반한다.
• 해시 조인은 어느 한쪽을 읽어 해시 맵을 만든다.
  • 둥 중 작은 집합을 해시 맵 Build Input 으로 선택하므로 두 집합 모두 Temp 로 갈정도로 크지 않는 한 Temp 즉 디스크 작업은 없다.
• 따라서 인메모리(In-Memory) 해시 조인이 가장 효과적이다.
• 설령 Temp 를 써도 대량 데이터 조인시 일반적으로 해시 조인이 가장 빠르다

4.3.3 대용량 Build Input 처리

• 두 테이블 모두 대용량으로 인메모리 해시조인이 불가능한 경우 분할 정복 방식으로 DBMS 는 해시조인 처리한다.

1. 파티션 단계

• 조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고 반환된 해시 값에 따라 동적으로 파티셔닝한다.
• 파티셔닝 된 양쪽 집합을 디스크 Temp 공간에 저장하므로 인메모리 해시 조인보다는 성능이 많이 떨어진다.

2. 조인 단계

• 각 파티션 짝에 대해 조인이 수행된다.
• 이때 각각에 대한 Build Input 과 Probe Input 은 독립적으로 결정된다.
  • 즉 작은쪽을 Build Input 으로 선택하고 진행된다.

4.3.4 해시 조인 실행계획 제어

• 위쪽(HASH JOIN 바로 아래) 사우너 데이터(Build Input) 로 해시 테이블을 생성한 후, 아래쪽 고객 테이블(Probe Input)에서 읽은 조인 키값으로 해시 테이블을 탐색하면서 조인한다고 해색하면 된다.(위쪽 Build Input 과 아래쪽 Probe Input 을 읽을 떄 인덱스를 이용한 사실도 실행계획에서 확인가능한다 물론 Table Full Scan 도 가능)

-- 위쪽 테이블 기준으로 해시 맵을 생성한 후, 아래쪽 테이블에서 읽은 조인 키값으로 해시 맵 탐색
-- 소트할 때 인덱스를 사용해서 인덱스로 표현된 것 뿐이지, 인덱스 없이 스캔했다면 TABLE FULL SCAN으로 나올 수 도 있음
HASH JOIN
  	TABLE ACCESS (BY INDEX ROWID) OF '사원' TABLE
  		INDEX (RAGNE SCAN) OF '사원_X1' (INDEX)
  	TABLE ACCESS (BY INDEX ROWID) OF '고객' TABLE
  		INDEX (RAGNE SCAN) OF '고객_X1' (INDEX)

• use_hash 힌트만 사용시 옵티마이저가 카디널리티가 작은 테이블을 일반적으로 선택
• 직접 선택하고 싶은 경우 leading 이나 ordered 힌트를 사용하면 된다.

세 개 이상 테이블 해시 조인

A,B,C 조인시 A<->B<->C 방식으로 조인하면 된다.

• leading 힌트로 지정시 leading 의 첫번째 파라미터로 지정한 테이블은 무조건 BUild Input 으로 선택된다.
• 가능한 패턴은 2가지이다.
• 변경 하고 싶은 경우 swap_join_inputs 힌트를 사용하라
• 조인한 결과집합을 Build Input 으로 사용하고 싶은 경우 no_swap_join_inputs 힌트를 사용하라

4.3.5 조인 메소드 선택 기준

• 반드시 선택에는 이유와 근거를 가지고 해야 한다.

1. 소량 데이터 조인 : NL 조인
2. 대량 데이터 조인 : 해시 조인
3. 대량 데이터 조인인데 해시 조인으로 처리할 수 없을 때, 즉 도인 조건식이 등치(=)조건이 아닐때(조인 조건식이 아예 없는 카테시안 곱 포함) : 소트 머지 조인

• 수행 빈도가 매우 높은 쿼리에 대해

1. (최적화된) NL 조인과 해시 조인 성능이 같은 경우 : NL 조인
2. 해시 조인이 약간 더 빨라도 : NL 조인
3. NL 조인보다 해시 조인이 매우 빠른 경우 : 해시 조인

• NL 조인에 사용하는 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조이다
• 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조이다.
• 따라서 수행시간이 짧으면서 수행빈도가 매우 높은 쿼리(OLTP) 를 해시 조인으로 처리하면 CPU 와 메모리 사용률리 증가하고 여러 해치 경합이 발생가능하다.
• 다음 조건시 해시 조인을 사용해라

1. 수행 빈도가 낮고
2. 쿼리 수행 시간이 오래 걸리는
3. 대량 데이터 조인할 때

• 이 세가지 조건은 배치프로그램, DW, OLAP 성 쿼리의 특징이다.