SQL 튜닝이론 - 해시조인의 특징과 유도방법

SQL 튜닝이론 - 해시조인의 특징과 유도방법

 

NL 조인은 인덱스를 이용한 조인 방식으로 인덱스 구성에 따른 성능 차이가 큽니다. 대량의 데이터를 처리하는 경우 인덱스 구성이 적절해도 과도한 TABLE RANDOM ACCESS로 인한 성능저하가 크고 버퍼 캐시 히트율에 따라 성능이 좌우됩니다. 소트 머지 조인과 해시 조인은 조인 과정에 인덱스를 이용하지 않기 때문에 대량 데이터를 조인할 때 NL 조인보다 훨씬 빠르고 일정한 성능을 유지합니다. 소트 머지 조인은 테이블 정렬에 대한 부담이 있는데 해시 조인은 정렬에 대한 부담이 없습니다.

 

해시조인의 기본 메커니즘

 

해시 조인은 두 단계로 진행됩니다

 

build 단계 : 작은 쪽 테이블을 읽어 해시 테이블을 생성합니다.

probe 단계 : 큰쪽 테이블을 읽어 해시 테이블을 탐색하면서 조인합니다.

 

아래는 해시조인으로 조인하도록 유도된 sql입니다.

 

SELECT /*+ORDERED USE_HASH(C)*/
*
FROM 사원 E, 고객 C
WHERE C.관리사원번호 = E.사원번호
AND E.입사일자 >= '199960101'
AND E.부서코드 = 'Z123'
AND C.최종주문금액 >= 20000

 

첫 번째, BUILD 단계로 조건에 해당하는 사원 데이터를 읽어 해시 테이블을 생성합니다. 조인칼럼인 사원번호의 해시함수 결괏값을 키로 사용합니다. 해시 테이블은 PGA 영역에 할당된 HASH AREA에 저장합니다. 해시 테이블이 너무 커서 PGA에 담을 수 없으면 TEMP 테이블스페이스에 저장합니다.

 

SELECT *
FROM 사원
WHERE 입사일자 >= '19960101'
AND 부서코드 = 'Z123'

 

두 번째, PROBE 단계로 아래 조건에 해당하는 고객 데이터를 하나씩 읽어 해시 테이블을 탐색합니다. 조인칼럼의 해시함 수 반환값을 해시 체인에서 찾고, 해시 체인을 스캔해서 값이 같은 사원번호를 찾습니다.

 

SELECT *
FROM 고객
WHERE 최종주문금액 >= 20000

 

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

 

begin
    for outer in (
        SELECT *
        FROM 사원
        WHERE 입사일자 >= '19960101'
        AND 부서코드 = 'Z123'
    )
    loop
        for inner in (
            selec 사원번호, 사원명, 입사일자 
            from  PGA에 생성된 사원 해시맵
            where 사원번호 = outer.관리사원번호
        )
        loop
            dbms_output.put_line(...);
        end loop;
   end loop;
end;

 

해시 조인이 빠른 이유

 

HASH AREA에 생성한 해시 테이블을 이용한다는 점만 다를 뿐 해시 조인도 조인 프로세싱은 NL조인과 같습니다. 그럼에도 해시 테이블을 PGA영역에 할당하기 때문에 속도면에서 우수합니다. 해시조인은 래치 획득 과정 없이 PGA에서 빠르게 데이터를 탐색하고 조인합니다. 해시 조인도 BUILD INPUT과 PROBE INPUT 각 테이블을 읽을 때는 DB 버퍼캐시를 경유합니다. 이때 인덱스를 이용합니다.

 

해시 테이블에 담기는 정보

 

인덱스에 저장된 ROWID로 테이블을 랜덤 액세스하는 NL 조인의 단점을 보완하기 위해 해시 테이블에는 조인 키 값뿐아니라 SELECT 절에 필요한 모든 컬럼 값을 저장하고 있습니다.

 

해시 조인과 소트 머지 조인, 둘 다 조인 오퍼레이션을 PGA에서 처리하는 공통점이 있습니다. 그런데 대량 데이터를 조인할 때 해시 조인이 더 빠릅니다. 차이는 조인 전 사전 준비작업 단계에서 발생합니다. 소트머지 조인은 양쪽 집합을 모두 정렬해서 PGA에 저장하는 사전 작업을 수행합니다. 반면, 해시 조인은 양쪽 집합 중 조건처리 후 결과집합이 작은 한쪽만을 읽어 해시 맵을 만드는 작업을 합니다. HASH AREA에 담기 힘들 정도로 큰 경우가 아니라면 TEMP 테이블 스페이스에 데이터를 쓰는 작업이 일어나지 않습니다.

 

해시조인은 NL 조인처럼 조인 과정에서 발생하는 랜덤 엑세스 부하가 없고, 소트머지 조인처럼 양쪽 집합을 미리 정렬하는 부하도 없습니다. 해시 테이블을 생성하는 비용이 수반되지만, 둘 중 작은 집합을 BUILD INPUT으로 선택하므로 부담이 크지 않습니다. BUILD INPUT이 PGA 메모리에 담길 때(INMEMORY HASH JOIN) 해시조인이 가장 효과적입니다. 설령 TEMP 테이블스페이스를 쓰게 되더라도 대량 데이터를 조회할 때는 일반적으로 해시종 니이 가장 빠릅니다.

 

대용량 BUILD INPUT 처리

 

조인할 두 테이블 모두 대용량 데이터일 때 해시 조인을 위한 BUILD INPUT을 처리하기 위해 분할-정복 방식을 사용합니다.

 

파티션 단계

 

조인할 양쪽 집합 (조인 조건 이외에 조건절을 만족하는 레코드)의 조인 칼럼에 해시 함수를 적용하고 반환된 해시 값에 따라 동적으로 파티셔닝 합니다. 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합을 분리하여 파티션 PAIR을 생성하는 단계입니다. 서브집합은 TMEP 공간에 저장되기 때문에 인메모리 해시 조인에 비해 성능이 많이 떨어집니다.

 

조인단계

 

각 파티션 PAIR에 대해 하나씩 조인을 수행합니다. 이때, 각각에 대한 BUILD INPUT과 PROBE INPUT은 독립적으로 결정됩니다. 파티션 하기 전 어떤 쪽이 작은 테이블이었는지 상관없이 각 파티션 중 작을 쪽을 BUILD INPUT으로 선택하고 해시 테이블을 생성합니다. 해시 테이블을 생성하고 나면 반대쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색합니다. 모든 파티션 짝에 대해 처리가 마칠 때까지 반복합니다.

 

해시 조인 실행계획 제어

 

-- 해시 조인 실행계획

SELECT STATEMENT OPTIMIZER=ALL_ROWS
HASH JOIN
    TABLE ACCESS BY INDEX ROWID OF 사원(TABLE)
        INDEX RANGE SCAN OF 사원_X1 INDEX
     TABLE ACCESS BY INDEX ROWID OF 괙ㄱ(TABLE)
        INDEX RANGE SCAN OF 고객_N1 INDEX

 

USE_HASH 힌트를 이용해 해시조인을 유도합니다.

 

SELECT /*+ORDERED USE_HASH(C)*/
*
FROM 사원 E, 고객 C
WHERE C.관리사원번호 = E.사원번호
AND E.입사일자 >= '199960101'
AND E.부서코드 = 'Z123'
AND C.최종주문금액 >= 20000

 

옵티마이저는 BUILD INPUT을 선택할 때 일반적으로 둘 중 카디널리티가 작은 테이블을 선택합니다. 사용자가 직접 BUID INPUT을 선택하려면 leading이나 ordered 힌트를 사용합니다. 옵티마이저는 사용자가 지시한 순서에 따라 가장 먼저 읽는 테이블을 build input으로 선택합니다.

 

SELECT /*+leading(e) USE_HASH(c)*/
*
FROM 사원 E, 고객 C
WHERE C.관리사원번호 = E.사원번호
AND E.입사일자 >= '199960101'
AND E.부서코드 = 'Z123'
AND C.최종주문금액 >= 20000

 

swap_join_inputs 힌트로 build input을 명시적으로 선택할 수도 있습니다. 고객 테이블을 build input으로 사용하도록 swap 합니다.

 

SELECT /*+leadingI(e) USE_HASH(C) swap_join_inpus(c)*/
*
FROM 사원 E, 고객 C
WHERE C.관리사원번호 = E.사원번호
AND E.입사일자 >= '199960101'
AND E.부서코드 = 'Z123'
AND C.최종주문금액 >= 20000

 

세 개 이상 테이블 해시 조인

 

A, B, C 세 개의 테이블이 존재할 때 조인하는 경로는 A와 B를 조인하고 B와 C를 조인하거나(A.key = B.key and B.key = C.key) A와 C를 조인하고 B와 C를 조인하는 방법(A.key = B.key and B.key = C.key)이 있습니다. 결과는 모두 같지만 테이블 구성에 따라 조인 과정에서 성능 차이가 발생합니다. 세 테이블에 대한 해시 조인을 제어할 때, leadning 힌트를 지정합니다.

 

SELECT /*+leading(T1,T2,T3) use_hash(T2) use_hash(T3) */
*
FROM T1,T2,T3
WHERE T1.KEY = T2.KEY
AND T2.KEY = T3.KEY

 

해시 조인에서 LEADING 힌트의 첫 번째 파라미터로 지정된 테이블은 BUILD INPUT으로 선택됩니다. 첫 번째 파라미터로 T1 테이블을 지정했으므로 T2 테이블과 조인할 때는 T1 이 BUILD INPUT, T2가 PROBE INPUT이 됩니다. 다음과 같은 두 패턴이 가능합니다.

 

-- 패턴 1
SELECT STATEMENT OPTIMIZE=ALL_ROWS
HASH JOIN
    HASH JOIN
            TABLE ACCESS FULL OF T1 TABLE
            TABLE ACCESS FULL OF T2 TABLE
   TABLE ACCESS FULL OF T3 TABLE

-- 패턴 2
SELECT STATEMENT OPTIMIZE=ALL_ROWS
HASH JOIN
    TABLE ACCESS FULL OF T3 TABLE
    HASH JOIN
            TABLE ACCESS FULL OF T1 TABLE
            TABLE ACCESS FULL OF T2 TABLE

 

T2를 BUILD INPUT으로 선택하려면 SWAP_JOIN_INPUT 힌트를 사용하면 됩니다. 실행 계획은 아래와 같이 변경됩니다.

 

SELECT /*+leading(T1,T2,T3) swap_join_inputs(T2) use_hash(T3) */
*
FROM T1,T2,T3
WHERE T1.KEY = T2.KEY
AND T2.KEY = T3.KEY

-- 패턴 1
SELECT STATEMENT OPTIMIZE=ALL_ROWS
HASH JOIN
    HASH JOIN
            TABLE ACCESS FULL OF T2 TABLE
            TABLE ACCESS FULL OF T1 TABLE
   TABLE ACCESS FULL OF T3 TABLE

-- 패턴 2
SELECT STATEMENT OPTIMIZE=ALL_ROWS
HASH JOIN
    TABLE ACCESS FULL OF T3 TABLE
    HASH JOIN
            TABLE ACCESS FULL OF T2 TABLE
            TABLE ACCESS FULL OF T1 TABLE

 

패턴 1을 패턴 2로 변경하는 방법은 T3를 BUILD INPUT으로 변경하는 방법은 아래와 같습니다.

 

SELECT /*+leading(T1,T2,T3) swap_join_inputs(T3)*/ ...
SELECT /*+leading(T1,T2,T3) swap_join_inputs(T2) swap_join_inputs(T3)*/ ...

 

패턴 1을 패턴 1로 변경하는 방법은 T3를 BUILD INPUT으로 지정하지 않도록 강제하는 것입니다. NO_SWAP_JOIN_INPUTS를 이용합니다.

 

SELECT /*+leading(T1,T2,T3) no_swap_join_inputs(T3)*/ ...

 

조인 메서드 선택 기준

 

일반적으로 소량의 데이터를 조인할 때 NL JOIN을 이용학소 대량 데이터를 조인할 때 해시 조인을 이용합니다. 그래고 대량 데이터인데 조인 조건절이 등치조건이 아닌 경우 혹은 조인 조건식이 아예 없는 경우(카테시안 곱) 소트 머지 조인을 이용합니다. NL 기준으로 최적화했음에도 랜덤 액세스가 많아 만족스러운 성능을 낼 수 없을 때 대량데이터로 취급합니다.

수행빈도가 높은 쿼리에 대해선 아래와 같은 기준을 적용할 수 있습니다.

 

• 최적화 된 NL 조인과 해시 조인의 성능이 같으면 NL 조인을 이용
• 해시 조인의 성능이 조금 더 높은 경우 NL조인 이용
• NL 조인보다 해시조인의 성능이 월등한 경우 해시조인

 

' NL 조인보다 해시조인의 성능이 월등한 경우'는 대부분 대량 데이터를 조인할 때입니다. SQL 최저고하시 옵티마이저가 수행빈도를 고려하지 않으므로 이 기준은 중요합니다.

 

조인 메서드를 선택할 때 항상 NL 조인이 우선적으로 고려되는 이유는 인덱스 영구적으로 유지되며 다양한 테이블 쿼리에 이용될 수 있기 때문입니다. 반면, 해시 테이블은 하나의 쿼리를 위해 생성하고 조인이 끝나면 소멸하는 자료구조입니다. 수행빈도가 높은 쿼리의 경우 해시조인을 이용하면 CPU와 메모리 사용이 크게 증가합니다.

 

결론적으로 해시조인은 아래 세 가지 조건을 만족할 때 사용합니다.

 

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

 

위 조건은 배치프로그램, DW, OLAP 성 쿼리의 특징이기도 합니다.