[SQL 튜닝 실습 2-3] 해시 조인 튜닝 개념

1) 해시 조인

두개의 테이블을 조인한다고 가정할 때, 작은 집합을 빠르게 읽어서 해시 테이블을 생성해서 해시 영역을 저장한 후,
큰 테이블을 순차적으로 읽으면서 해시 함수에 입력 값을 줘서 해시영역에 있는 해시테이블(작은 집합) 내에 해당 값이 
존재하면, 조인 집합에 저장해서 원하는 조인 결과를 도출해내는 방식

일반적으로 대용량 테이블의 조인 연산에서는 해시 조인 방식이 중첩 루프 조인이나 소트 머지 조인 방식보다 훨씬 효율적이다
즉, 작은 집합과 큰 집합이 있는 상황에서 오라클의 해시 조인은 극적인 성능 향상을 이뤄냅니다
근데, 해시 조인은 대용량 테이블 조인 시에 메모리가 많이 필요하다
해시 조인의 성능을 극대화하려면 오라클 DBMS에 대한 메모리 관리도 신경을 써야 한다

 

2) 해시 조인의 특성
* 두 개의 테이블 중 한 테이블이 작은 집합이어야 성능 극대화가 가능합니다.
* 조인 조건이 반드시 '=' 조건이어야 한다

일반적인 업무에서 두 개의 테이블이 1:M 관계에 놓였을 경우,한쪽 집합이 훨씬 작은 용량의 테이블인 경우가 많다
이러한 상황에서 한쪽 집합을 Build Input 으로해서 해시 조인을 수행한다면 극적인 성능 향상을 이뤄낼 수 있다.

 

3) Build Input과 Probe Input

Build Input

해시 조인 시 해시 영역에 저장하는 집합을 뜻한다.
중첩 루프 조인 기준으로는 Outer 테이블 이라고 이해하면 된다.
Build Input은 반드시 작은 집합이어야 해시 영역에 메모리 공간을 초과하지 않고 들어갈 수 있다
Build Input이 지나치게 큰 테이블이 된다면 오히려 메모리 영역과 디스크 영역 사이에 페이징이 발생하게 되서
성능이 떨어질 위험이 있다.

Probe Input

해시 조인 시 해시 영역에 저장된 Build Input의 데이터가 해시 방식 접근으로 조인을 수행하는 집합을 뜻한다
해시 영역에 생성된 해시 테이블이 구성되면 Probe Input을 순차적으로 스캔하면서 해시 함수를 통한 해시 테이블 검색을 하게 된다. 
이 때 Probe Input은 큰 용량의 테이블을 지정해야 한다

 

4) 해시 조인을 위한 메모리 관리

오라클의 PGA 영역은 해시 조인 시 사용하게 되는 메모리 영역이다.
해당 해시 조인 시 Build Input이 PGA 영역에 모두 담길 정도로 작다면 최적 연산이 일어나면서 극적인 성능을 발휘한다
하지만 Build Input이 너무 커서 PGA 영역에 모두 담지 못하게 되면 임시 공간 영역을 이용하게 되면서 디스크 I/O 가 발생하게 된다
즉, 멀티 패스 연산이 일어나게 된다.
이런 경우에는 PGA_AGGRATE_TARGET 파라미터의 메모리 크기를 확장해서 성능 향상을 꾀할 수 있는데, 
다음 명령어로 이를 수행할 수 있다

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 66; 

 

5) 해시 조인 튜닝
기존의 해시 조인 방식이 아닌 중첩 루프 조인이나 소트 머지 조인 방식으로 되어있는 SQL을 해시 조인 연산 조건에 부합되는지
분석한 후 해시 조인 방식으로 바꾸는 일련의 모든 활동을 뜻한다

6) 해시 조인 튜닝 관련 힌트

USE_HASH 옵티마이저에 해시 조인을 유도한다.

입력한 두 집합 중 작은 집합을 Build Input 으로 지정해서 큰 집합인 Probe Input과 해시 조인을 수행한다

 

SELECT /*+ USE_HASH(테이블 테이블) */			
FROM EMP, DEPT			
WHERE EMP.DEPTNO = DEPT.DEPTNO ;			

 

LEADING 힌트랑 결합해서 Build Input을 직접 지정하는 방법도 있다. 
제일 많이 쓰이는 방식은 다음과 같다

DEPT 테이블을 선행 테이블로 삼고, EMP 테이블을 Probe Input 으로 한다
즉, 선행 테이블인 DEPT 테이블이 Build Input 이 되고 EMP 테이블은 Probe Input이 된다

 

SELECT /*+ LEADING(DEPT) USE_HASH(EMP) */ *				
FROM EMP, DEPT				
WHERE EMP.DEPTNO = DEPT.DEPTNO;