친절한 SQL 튜닝_4장.조인 튜닝_3

조인 튜닝

 

4.3. 해시 조인

 

4.3.1 기본 메커니즘

 

해시 조인(Hash Join)도 소트 머지 조인처럼 두 단계로 진행된다.

 

1. Build 단계 : 작은 쪽 테이블(Build Input)을 읽어 해시 테이블(해시 맵)을 생성한다.

 

2. Probe 단계 : 큰 쪽 테이블(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인한다.

 

 

 

NL 조인과 소트 머지 조인에서 사용했던 아래 SQL로 해시 조인 과정을 설명해 보자. 해시 조인은 use_hash 힌트로 유도한다. 아래 SQL에 사용한 힌트는, 사원 테이블 기준으로 (ordered) 고객 테이블과 조인할 때 해시 조인 방식을 사용하라(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에 저장한다. 해시 테이블이 너무 커 PGA에 담을 수 없으면, Temp 테이블스페이스에 저장한다.

 

SELECT 사원번호, 사원명, 입사일자
  FROM 사원
 WHERE 입사일자 >= ‘19960101’
   AND 부서코드 = ‘Z123’

 

 

 

2. Probe 단계 : 아래 조건에 해당하는 고객 데이터를 하나씩 읽어 앞서 생성한 해시 테이블을 탐색한다. 즉, 관리사원번호를 해시 함수에 입력해서 반환된 값으로 해시 체인을 찾고, 그 해시 체인을 스캔해서 값이 같은 사원번호를 찾는다. 찾으면 조인에 성공한 것이고, 못 찾으면 실패한 것이다.

 

SELECT 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
  FROM 고객
 WHERE 최종주문금액 >= 20000

 

 

Build 단계에서 사용한 해시 함수를 Probe 단계에서도 사용하므로 같은 사원번호를 입력하면 같은 해시 값을 반환한다. 따라서 해시 함수가 반환한 값에 해당하는 해시 체인만 스캔하면 된다.

 

Probe 단계에서 조인하는 과정을 PL/SQL 코드로 표현하면 아래와 같다.

 

 

1 2 3 4 5 6 7 8 9 10 11 12 13

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; Colored by Color Scripter

cs

 

 

실제 조인을 수행하는 2번 Probe 단계는 NL 조인과 다르지 않다는 사실을 위 Pseudo 코드를 통해 알 수 있다.

 

 

 

4.3.2 해시 조인이 빠른 이유

 

Hash Area에 생성한 해시 테이블(=해시 맵)을 이용한다는 점만 다를 뿐 해시 조인도 조인 프로세싱 자체는 NL 조인과 같다. 그런데도 해시 조인이 인덱스 기반의 NL 조인보다 빠른 결정적인 이유는, 소트 머지 조인이 빠른 이유와 같다. 즉, 해시 테이블을 PGA 영역에 할당하기 때문이다. NL 조인은 Outer 테이블 레코드마다 Inner 쪽 테이블 레코드를 읽기 위해 래치 획득 및 캐시버퍼 체인 스캔 과정을 반복하지만, 해시 조인은 래치 획득 과정 없이 PGA에서 빠르게 데이터를 탐색하고 조인한다.

 

해시 조인도 Build Input과 Probe Input 각 테이블을 읽을 때는 DB 버퍼캐시를 경유한다. 이때 인덱스를 이용하기도 한다. 이 과정에서 생기는 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 해시 조인이라도 피할 수 없다.

 

해시 조인은 NL 조인처럼 조인 과정에서 발생하는 랜덤 액세스 부하가 없고, 소트 머지 조인처럼 양쪽 집합을 미리 정렬하는 부하도 없다. 해시 테이블을 생성하는 비용이 수반되지만, 둘 중 작은 집합을 Build Input으로 선택하므로 대개는 부담이 크지 않다. Build Input이 PGA 메모리에 담길 때, 즉 인메모리(In-Memory)해시 조인일 때 가장 효과적인 이유가 바로 여기에 있다.

 

그렇다고 Build Input이 Hash Area 크기를 초과하면 다른 조인 메소드를 선택하라는 뜻은 아니다. 설령 Temp 테이블스페이스를 쓰게 되더라도 대량 데이터 조인할 때는 일반적으로 해시 조인이 가장 빠르다.

 

 

 

4.3.3 대용량 Build Input 처리

 

DBMS는 어떤 방식으로 해시 조인을 처리할까? 복잡할 것 같지만, 의외로 간단하다. 아래 두 단계로 나눠서 진행된다. 분할〮정복(Divide & Conquer) 방식이다.

 

1.파티션 단계

 

조인하는 양쪽 집합(-> 조인 이외 조건절을 만족하는 레코드)의 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝한다. 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝(pair)을 생성하는 단계다.

 

2.조인 단계

 

파티션 단계를 완료하면 각 파티션 짝(pair)에 대해 하나씩 조인을 수행한다. 이때, 각각에 대해 Build Input과 Probe Input은 독립적으로 결정된다. 즉, 파티션하기 전 어느 쪽이 작은 테이블이었는지 상관없이 각 파티션 짝(Pair)별로 작은 쪽을 Build Input으로 선택하고 해시 테이블을 생성한다.

 

해시 테이블을 생성하고 나면 반대쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색한다. 모든 파티션 짝에 대한 처리를 마칠 때까지 이 과정을 반복한다.

 

 

 

4.3.4 해시 조인 실행계획 제어

 

해시 조인 실행계획을 제어할 때 아래와 같이 use_hash 힌트를 사용한다.

 

SELECT /*+ 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

 

여기서는 use_hash 힌트만 사용했으므로 Build Input을 옵티마이저가 선택했는데, 일반적으로 둘 중 카디널리티가 작은 테이블을 선택한다.

 

Build Input을 사용자가 직접 선택하고 싶다면 어떻게 할까? 조인 대상 테이블이 두 개뿐이라면 아래와 같이 leading이나 ordered 힌트를 사용하면 된다. 이들 힌트로 지시한 순서에 따라 먼저 읽는 테이블을 Build Input으로 선택한다.

 

SELECT /*+ leading(e) use_hash(c) */ -- 또는 ordred 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

 

아래와 같이 swap_join_inputs 힌트로 Build Input을 직접 선택하는 방법도 있다.

SELECT /*+ use_hash(e c) swap_join_inputs(e) */
       e.사원번호, e.사원명, e.입사일자
     , c.고객번호, c.고객명, c.전화번호, c.최종주문금액
  FROM 사원 e, 고객 c
 WHERE c.관리사원번호 = e.사원번호
   AND e.입사일자 >= ‘19960101’
   AND e.부서코드 = ‘Z123’
   AND c.최종주문금액 >= 2000

 

 

 

4.3.5 조인 메소드 선택 기준

 

1.소량 데이터 조인할 때 -> NL 조인

 

2.대량 데이터 조인할 때 -> 해시 조인

 

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

 

여기서 소량과 대량의 기준은 무엇일까? 이는 단순히 데이터량의 많고 적음에 있지 않다. NL 조인 기준으로 ‘최적화했는데도’ 랜덤 액세스가 많아 만족할만한 성능을 낼 수 없다면, 대량 데이터 조인에 해당한다.

 

 

수행빈도가 매우 높은 쿼리에 대해선 아래와 같은 기준도 제시하고 싶다.

 

1.(최적화된) NL 조인과 해시 조인 성능이 같으면, NL 조인

 

2.해시 조인이 약간 더 빨라도 NL 조인

 

3.NL 조인보다 해시 조인이 매우 빠른 경우, 해시 조인

 

조인 메소드를 선택할 때 NL 조인을 가장 먼저 고려해야 할까? NL 조인 위주로 처리하려면 인덱스를 세심하게 설계해야 하는 부담도 있는데 말이다. 심지어, 해시 조인이 약간 더 빠른데도 왜 NL 조인을 선택해야 할까?

 

NL 조인에 사용하는 인덱스는 (DBA가 Drop하지 않는 한) 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조다. 반면, 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 바로 소멸하는 자료구조다.

 

 

해시 조인은 아래 세 가지 조건을 만족하는 SQL문에 주로 사용한다.

 

1.수행 빈도가 낮고

 

2.쿼리 수행 시간이 오래 걸리는

 

3.대량 데이터 조인할 때

출처 : 친절한 SQL 튜닝 - 조시형 지음