[오라클 DB조인] 해시 조인(Hash Join)의 원리와 사용방법(힌트 및 플랜확인)

1. 해시 조인의 개념

해시 조인은 소트머지 조인처럼 2단계로 진행이 된다
Build 단계 : 작은 쪽 테이블(Build Input)을 읽어 해시 테이블(해시 맵)을 생성
Probe 단계 :  큰 쪽 테이블(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인
 
해시 조인이 사용되는 상황으론
1) 대량 데이터를 조인할 떄 사용됨(조인 조건이 등치(=) 조건일 떄만 사용가능)
 

2. 해시 조인의 동작원리

해시 조인의 동작원리는 단순히 글로만 적기엔 이해가 힘들 수 있으므로,
아래의 쿼리를 기반으로 적어보려고 한다.

<예문 쿼리>

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.최종주문금액 >= 20000

 

위의 쿼리를 그림으로 풀어보면 아래와 같다.
 

 
1) Build 단계 

사원 테이블을 조인 컬럼인 사원번호를 해시 테이블의 키값으로 사용하여 사원 해시 테이블을 생성한다.
위의 사원 해시 테이블의 데이터를 보면
(0003/김철수/19960712) 예문 쿼리에서 출력이 필요한 모든 값들을  해시 테이블로 가져간걸 확인 가능하다
 
해시 테이블에 담기는 정보의 종류에 대해서 오해할 수 있는 부분이 있는데 해당 오해는 바로 아래의 표에서 풀고 넘어가보자
 

**해시 테이블에 담기는 정보 (친절한 SQL 튜닝 책의 내용을 참조)

해시 테이블에 조인 키값만 저장한다고 알고 있는 분이 많다. 위의 그림 4-10으로 말하면 사원 해시 테이블의 "사원번호"만을 말한다. 하지만 실제로 사원 해시 테이블을 보면 사원번호 뿐만 아니라 모든 데이터가 들어있는걸 볼 수 있다. (0003/김철수/19960712)  그런데 만약 해시 테이블에 키값만을 저장한다면, 래치 획득 과저 없이 PGA에서 조인한다는 해시 조인의 장점이 사라진다. 조인에 성공한 사원번호에 대한 나머지 정보(사원명.입사일자)를 읽으려면 ROWID로 다시 테이블 블록을 액세스해야 하기 때문이다. 인덱스 ROWID로 테이블을을 랜덤 액세스하는 NL조인의 단점 떄문에 소트머지조인과 해시조인이 탄생했다는 사실을 상기하기 바란다.

 
 
그리하여 Build단계에선 먼저  아래의 쿼리가 동작했다고 보면된다

<Build 단계의 쿼리>

select 
    e.사원번호, .e,사원명, e.입사일자
FROM 사원 e
WHERE   e.입사일자 >= '19960101'
      AND e.부서코드 = 'Z123'

 
2) Probe 단계

해당 단계에선 먼저 키값을 제외한 컬럼을 사용하여 전체 데이터를 조회한다

닫기

<Probe 단계의 쿼리>

select  

    c.고객번호, c.고객명, c.전화번호, c.최종주문금액, c.관리사원번호

FROM 고객 c

WHERE c.최종주문금액 >= 20000

위에서 출력된 데이터 중 관리사원번호(조인 컬럼)을 사용하여 해시 함수(F(x))를 대입하여 사원 해시 테이블을 찾아가며 데이터를 조회한다.
( 아래 사진의 "고객 테이블"은 Probe 단계의 쿼 결과 모습)

 

 

3. 해시조인이 빠른 이유

소트머지조인과 같은 이유이다
소트머지가 빠른 이유는 아래에서 확인!
[IT 공부/SQLP] - [SQLP] 소트 머지 조인(sort Merge Join)의 모든것-친절한Sql튜닝

[SQLP] 소트 머지 조인(sort Merge Join)의 모든것-친절한Sql튜닝

 
하지만 또다시 소트머지조인과 해시조인을 비교한다면 해시조인이 조금 더 빠르다고 볼 수 있다.
소트머지 조인을 생각해보면 소트단계에서 양쪽의 테이블을 모두 정렬을 진행하여야 한다.
이 떄 양쪽 테이블 중 어느 하나가 대량의 데이터를 들고있다면 PGA에 넣는 효과를 온전히 사용할 수 없어(Temp테이블스페이스르 별도로 사용, 결국 디스크에 쓰는 작업 필요) 그 만큼 효과가 떨이지게 된다.
해시조인은 양쪽 집합 중 '한쪽'만을 읽어 해시 맵을 만드는 작업을 따른다. 
그렇기에 양쪽 집합중 작은 한쪽만 별도의 작업이 필요하니 그렇게 PGA에 많은 공간을 필요로 하지 않읗 수 있기 때문이다.

4.해시조인 실행계획 제어

select  /*+ use_hash(e c)  */
    e.사원번호, .e,사원명, e.입사일자
    c.고객번호, c.고객명, c.전화번호, c.최종주문금액
FROM 사원 e, 고객 c
~~
use_hash : 해시조인 사용 힌트
* 위처럼 서 단순하게 use_hash만을 사용하게 된다면 Build Input으로 사용되는 테이블은 옵티마이저가 선택하는데,
일반적으론 선택되는 컬럼이 적은 테이블을 선택한다
 
만약 Build input을 직접적으로 선택하고 싶다면 swap_join_inputs(테이블명)을 작성하면 된다
예시로 /*+ use_hash(e c)  swap_join_inputs(e) */ 와 같이 사용가능하다.
 
이와는 반대로 Probe Input을 별도로 선택하고 싶다면 no_swap_join_inputs(테이블명)을 작성하면 된다
예시로 /*+ use_hash(e c)  no_swap_join_inputs(c) */ 와 같이 사용가능하다.
 
결국 
/*+ use_hash(e c)  swap_join_inputs(e) */ 와  /*+ use_hash(e c)  no_swap_join_inputs(c) */  같은 명령으로 볼 수 있다
 
아래는 사원 테이블이 Buill input으로 사용된 실행계획(플랜)이다

 

 

5. 해시조인의 특징 마무리 요약

해시조인이 매우 빠른 조인 방식이지만 아래와 같은 상황에서 가장 큰힘을 발휘할 수 있다.
1) 수행 빈도가 낮으면
2) 쿼리 수행 시간이 오래 
3) 대량 데이터를 조회할 때
 
위의 말은 다시 말하면 온라인 환경에서보다 배치 프로그램,DW,OLAP 환경에서 해시조인을 사용이 어느정도 적합하다고 볼 수 있다.
 
테스트 데이터 생성 방법:

2025.03.20 - [IT/SQLP] - [데이터 생성] 오라클 DB 데이터 생성 쿼리모음(테이블,인덱스,데이터 등)