SQL튜닝 - 해시조인을 활용한 조인튜닝방법 ( 조인속도, 유도방법, SWAP_JOIN_INPUTS, NO_SWAP_JOIN_INPUTS, 사용기준)

 

SQL튜닝 - 해시조인을 활용한 조인튜닝방법 ( 조인속도, 유도방법, SWAP_JOIN_INPUTS, NO_SWAP_JOIN_INPUTS, 사용기준)

 

 

해시조인은 NL JOIN 이나 SORT MERGE JOIN 의 비효율을 해결하기 위해 나온 해시맵을 사용하는 조인 방식입니다. 해시 조인은 둘 중 작은 집합을 읽어 해시 영역에서 해시 테이블을 생성하고 큰 집합을 읽어 해시 테이블을 탐색하면서 조인하는 방식입니다. 해시 함수는 동이한 입력값에 대해 동일한 출력값을 보장합니다. 다른 입력값에 대한 출력값이 같은 '해시충돌'이 발생하면, 입력값이 다른 엔트리가 같은 해시버킷에 담길 수 있습니다.

 

해시조인의 특징

해시조인의 프로세싱 자체는 NL 조인과 동일합니다. 건건이 INNER 집합을 버퍼케시에서 탐색하지 않고 PGA에 미리 생성해 둔 해시 테이블을 탐색하면서 조인한다는 점에서 다릅니다. 해시맵을 이용하므로 조인과정에서 인덱스가 없어도 성능상 문제가 없습니다. 해시 맵을 PGA에 생성해야 하므로 두 테이블 중 어느 한쪽이 충분히 작아야 효과적입니다. 단, 양쪽 모두 PGA를 초과하더라도 대량의 데이터 조인에는 NL 조인보다 해시조인이 빠릅니다. 알고리즘 특성상 조건절에 하나 이상의 '=' 가 있어야 사용할 수 있습니다. 해시조인은 수행빈도가 낮고 쿼리 수행 시간이 오래걸리는 대량 데이터를 조회할 때 사용하는 것이 좋습니다. 해시 테이블은 조인 후 메모리에서 사라집니다.

 

해시조인이 빠른 이유

 

해시 조인이 인덱스 기반 조인(NL join) 보다 빠른 이유는 해시 테이블을 PGA에 할당하기 때문입니다. NL 조인은 OUTER 테이블 레코드마다 INNER 테이블 쪽 테이블 레코드를 읽기 위해 래치 획득 및 캐시버퍼 체인 스캔 과정이 필요합니다. 반면, 해시 조인은 래치 획득 과정없이 PGA에서 빠르게 데이터를 탐색하고 조인합니다. 해시 조인도 BUILD INPUT 과 PROBE INPUT 을 만들때는 DB 버퍼캐시를 공유합니다. 이때는 인덱스를 이용합니다.

 

소트머지 조인보다 해시조인이 빠른이유는 사전 준비작업의 속도차이 때문입니다. 해시조인에서 사전 준비작업은 양쪽 중 어느 한쪽을 읽어 해시 맵을 만드는 작업입니다. HASH AREA에 담을 수 없을 정도로 큰 경우가 아니라면 TEMP TABLE SPACE 즉, 디스크에 쓰는 작업이 발생하지 않습니다.

해시조인의 조인조건이 = 일때만 사용가능한 이유

해시 함수를 이용해 체인을 할당하고 값을 찾는 특성으로 인해 해시 조인은 조인 조건이 = 일때만 사용할 수 있습니다. = 조건이 하나 이상이면 해시 함수의 결과을 기준으로 하나의 체인에 달리는 레코드의 수가 적어져 맵 탐색 효율이 높아집니다. 해시 체인에 연결된 값이 많을 수록 해시맵 탐색 효율은 나빠지기 때문입니다.

 

해시조인 유도방법 - ORACLE

 

해시 조인은 use_hash(PRODE_TABLE_NAME) 힌트로 유도하고 일반적인 실행계획은 다음과 같습니다.

SELECT /*+ordered use_hash(e)*/
d.deptno, d.dname, e.empno, e.ename
from dept d, emp e
where d.detpno = e.deptno

 

해시조인 유도방법 - SQL SERVER

 

SELECT *
FROM 주문 O
inner hash join 고객 c on (o.고객번호 = c.고객번호)
where o.주문일자 >= TRUNC(SYSDATE)
OPTION (FORCE ORDER)

SELECT *
FROM 주문 O
inner join 고객 c on (o.고객번호 = c.고객번호)
where o.주문일자 >= TRUNC(SYSDATE)
OPTION (FORCE ORDER, HASH JOIN)

 

해시조인 순서 변경방법

 

NO_SWAP_JOIN_INPUTS

 

FROM 절 역순으로 조인하도록 순서를 변경하기위해서는 힌트를 활용해야 합니다. swap_join_input(table_name) 은 BUILD INPUT을 table_name 로 변경합니다. 실행계획상 HASH JOIN 오퍼레이션 바로 아래 테이블이 BUILD INPUT이 됩니다. LEADING 또는 ORDERED 에 의해 선택된 첫번째 테이블은 항상 build input이 됩니다. 이 경우 use_hash 힌트만으로는 변경이 불가합니다.

 

SELECT 
/*+leading(d) use_hash(e)*/
/*+ordered use_hash(d) swap_join_input(d)*/
/*+use_hash(e d) swap_join_inputs(d)*/ * 
FROM EMP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO

-- execution plan
-- select statement
--    hash join
--      table access full of dept
--      table access full of emp

 

NO_SWAP_JOIN_INPUTS

 

probe input을 명시적으로 선택할 때는 no_swap_join_inputs 힌트를 사용합니다.

 

select
/*+LEADING(A, B, C) USE_HASH(B) USE_HASH(C) NO_SWAP_JOIN_INPUTS(C)*/
*
from 주문 a, 주문상품 b, 상품 c
where b.주문번호 = a.주문번호
and c.상품코드 = b. 상품코드

-- Execution plan
SELECT STATEMENT
  HASH JOIN
    HASH JOIN
      TABEA ACCESS FULL OF 주문
      TABEA ACCESS FULL OF 주문상품
    TABLE ACCESS FULL OF 상품

 

해시조인 진행과정

 

 

해시 조인은 다음과 같이 진행됩니다.

 

1단계(BUILD PHASE)

조인 대상 테이블의 결과집합이 작다고 판단되는 집합(BUILD TABLE)을 읽어 해시 테이블을 생성합니다. 해시 테이블은 해시 버킷으로 구성된 배열형태의 자료구조입니다. 해시함수에서 리턴받을 해시 값이 같은 데이터를 같은 해시 버킷에 체인으로 연결합니다. 이 과정을 통해 생성한 테이블을 BUILD INPUT이라 합니다.

 

2단계 (PRODE PHASE)

 

큰쪽 집합을 스캔합니다. 큰쪽 집합을 PROBE INPUT이라 합니다. PROBE INPUT을 해시 함수에 대입하여 리턴받은 버킷 주소로 BUILD INPUT의 버킷을 찾아가 해당 레코드와 조인합니다. 해시 조인은 조인과정에서 발생하는 랜덤 엑세스나 정렬에 대한 부담이 없습니다. 다난 해시 테이블을 생성하는 비용이 듭니다. 따라서 해시 테이블을 만들 BUILD INPUT이 작을때 효과적입니다. 해시 테이블을 만들 메모리 공간을 초과할 정도로 BUILD INPUT이 대용량 테이블이라면 디스크 I/O가 발생하기 때문에 성능이 저하됩니다. BUILD INPUT이 작은 것도 중요하지만 해시 키 값으로 사용되는 칼럼에 중복이 적어야 효과적입니다. 해시테이블을 만드는 단계는 전체범위처리이지만 PRODE INPUT을 스캔하는 단계는 NL 조인처럼 부분범위처리가 가능합니다.

 

요약하면 다음과 같습니다.

 

작은 테이블을 읽는다 - 해시맵을 만든다 - 큰 테이블을 스캔한다 - 해시 맵을 탐색한다 - 조인결과를 반환한다.

 

BUILD INPUT이 가용 메모리 공간을 초과할때 처리방식

 

 

IN-MEMORY (가용 메모리 공간 내에서 처리) 해시 조인이 불가능하면 DBMS는 GRAGE HASH JOIN 알고리즘을 사용합니다.

 

- 파티션 단계

 

조인 대상 테이블 양쪽에 모두 해시 함수를 적용하고 반환된 해시 값에 따라 동적으로 파티셔닝합니다. 결과집합을 독립적으로 처리할 수 있는 여러 서브 집합으로 분할하여 파티션 짝을 생성합니다. 디스크 I/O가 발생하므로 성능이 떨어집니다.

 

- 조인 단계

 

파티셔닝이 완료되면 파티션 짝에 대한 조인을 수행합니다. 이때 각 조인에 대한 BUILD INPUT과 PROBE INPUT이 독립적으로 결정됩니다. 즉, 파티션하기 전 어느 쪽이 작은 집합이었는지 상관없이 파티션 짝 별로 작은 파티션을 BUILD INPUT으로 선택합니다. 모든 파티션 짝에 대한 조인 처리가 완료될때까지 반복합니다.

 

- RECURSIVE 해시 조인

 

파티션 짝끼리 조인을 수행하는 과정에서 또다시 가용 메모리를 초과하면 추가적인 파티셔닝을 진행합니다.

 

BUILD INPUT 해시 키 값에 중복이 많을 때 발생하는 비효율

 

해시 알고리즘의 성능은 충돌을 얼마나 최소화할 수 있느냐에 달려있습니다. DBMS는 가능한 충분히 많은 수의 버킷을 할당하여 버킷 당 하나의 엔트리만 있게 하려고 노력합니다.

 

해시조인 사용기준

 

해시 조인의 성능 포인트는 다음과 같습니다.

 

- BUILD INPUT이 가용 메모리에 담길 정도로 충분히 작아야함

- BUILD INPUT 해시 키 칼럼에 중복이 없어야함.

 

해시 조인을 효과적으로 사용할 수 있는 상황은 다음과 같습니다.

 

- 조인 칼럼에 적당한 인덱스가 없어 NL 조인이 비효율적일 때.
- DRIVING 집합에서 CHECK 집합으로 조인 엑세스가 많아 랜덤 엑세스 부하가 심할 때
- 두 테이블이 커서 소트부하가 심할때
- 수행빈도가 낮고 쿼리 수행 시간이 오래걸리는 대용량 테이블을 조인할 때

 

해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 소멸됩니다. 따라서 CPU 메모리 사용률을 크게 증가시키고 메모리 자원 확보를 위해 시스템의 동시성을 떨어뜨리는 단점이 있어 신중한 사용이 요구됩니다.