오라클 Hash Join 원리와 use_hash 힌트 사용법

오라클 Hash Join 원리와 use_hash 힌트 사용법

해시 조인(Hash Join)은 관계형 데이터베이스의 조인 방식 중 하나로, 대용량 데이터를 효율적으로 조인하는 데 유용하며, 메모리를 활용한 고속 연산이 특징이다.

 

작동 원리

1. Build Phase (작은 테이블 적재)

두 테이블 중 크기가 작은 쪽을 Inner Table로 선택하여 메모리에 해시 테이블을 생성한다.

2. Probe Phase (큰 테이블 스캔)

Inner Table을 해시 함수로 해싱한 해시 테이블을 활용하여 Outer Table(큰 테이블)을 스캔한다.

3. 결과 반환

해시 테이블에서 매칭되는 해시 버킷을 찾으면, 해당 버킷에 속하는 Inner Table의 행과 Outer Table의 현재 처리 중인 행을 조인하여 결과를 반환한다.

---

장점

빠르다.

대용량 데이터 조인에 효과적이다.

단점

해시 테이블 생성에 메모리 비용이 발생한다

---

USE HASH 힌트

아래와 같이 작성 시 옵티마이저가 통계정보를 근거로 더 작은 테이블을 Build Input으로 선택하여 Hash Join 한다.

select /*+ use_hash(m p) */
	m.mbr_no, m.mbr_id, p.pay_no, p.pay_time
from member m, pay p
where m.mbr_no = p.mbr_no

 

Build Input을 지정하고 싶을 경우 swap_join_input, ordered, leading 힌트를 같이 사용할 수 있다.

select /*+ leading(m) use_hash(p) */  -- m을 먼저 leading 하여 build input으로 사용하도록 지정
	m.mbr_no, m.mbr_id, p.pay_no, p.pay_time
from member m, pay p
where m.mbr_no = p.mbr_no

select /*+ use_hash(m p) swap_join_input(m) */  -- m을 build input으로 사용하도록 지정
	m.mbr_no, m.mbr_id, p.pay_no, p.pay_time
from member m, pay p
where m.mbr_no = p.mbr_no

 

이때 ordered나 leading 힌트의 역할은 조인의 순서를 결정하기 위한 것이지, Build Input을 결정하기 위한 것은 아니다. 두 개의 테이블을 조인할 때는 위 예시처럼 사용이 가능하지만, 두 개 이상의 여러 테이블을 조인 할 때에는  ordered나 leading 힌트만으로 Build Input을 모두 지정할 수 없으며 여러 테이블 중 처음 조인되는 두 개의 테이블 중 Build Input을 정하는데에만 영향을 줄 수 있다.

여러집합을 해시조인하면서, 집합간의 Build Input을 사용자가 직접 조정하려면 swap_join_inputs 힌트를 사용해야 한다.

 

---

 

약 200만건 테이블과 300만건 테이블을 조인하는 일 배치성 쿼리 튜닝 요청을 받았다. 조건 범위가 넓어 최적의 인덱스를 생성하더라도 효율이 좋지 않고, 하루에 한 번 수행되는 점을 고려하여 기존 nested loop join을 하고있던 부분에 hash join 힌트를 주었다. 그 결과 기존 3분정도 소요되던 쿼리를 1분 20초정도로 단축할 수 있었다.

 

hash join을 쓸때는 원하는 쿼리 결과를 얻기 위해 대량 조인이 불가피한지, 다른 방법으로 결과를 얻을 수 없는지 먼저 검토하고, 쿼리가 얼마나 자주 수행되는지, 쿼리가 어느정도 속도로 수행되어야 하는지, 얼마나 중요한 쿼리인지를 잘 저울질하여 사용해야할 것 같다. 또한 신규 컬럼이나 테이블을 추가하고 로직을 변경하는 방안도 고려해본다면 좋을 것 같다.