[튜닝] 해쉬 조인 (USE_HASH)

해쉬 조인 기본 구조

● BUILD 테이블

  - 먼저 엑세스 되는 테이블

  - 해쉬 영역을 만드는 역할

  - 조건을 만족하는 데이터에 대해 한 번만 엑세스

● PROBE 테이블

  - 뒤에 엑세스되는 테이블

  - 해쉬 영역을 증명하는 역할

  - 조건을 만족하는 데이터에 대해 한 번만 엑세스

 

TABLE_A 테이블에서 조건에 만족하는 하나의 값을 추출하여 조인 컬럼에 해쉬 함수를 적용한다. 해쉬 함수를 적용하여 추출된 값을 통해 해당 데이터가 저장될 파티션을 결정한다. 그다음에 두 번째 해쉬 함수를 적용하여 해당 파티션 내의 어느 서브 파티션에 TABLE_A의 데이터가 저장될지를 결정한다. 서브 파티션이 결정되었다면 SQL을 수행하기 위해 필요한 데이터를 추출하여 해당 서브 파티션에 저장한다.

해쉬 함수가 적용된 값을 이용하여 해쉬 테이블을 생성하며, 해쉬 조인의 성능을 향상시키기 위해 조인 컬럼의 값을 비트맵 영역에 저장한다.

TABLE_A에 대해 해쉬 영역 구성이 끝나면 PROBE 테이블(TABLE_B)을 엑세스 한다.

TABLE_B에서 WHERE 조건(조인 컬럼 아님)에 만족하는 데이터에 엑세스하여 첫 번째 해쉬 함수를 적용하고, 해당 해쉬 함수의 결과 값으로 비트맵 영역을 확인한다. (비트맵 영역에 존재하면 조인 성공, 없으면 실패)

첫 번째 해쉬 함수를 적용한 값이 비트맵 영역에 존재하는 TABLE_B 테이블의 데이터에 대해 두 번째 해쉬 함수를 적용한다. 두 번째 해쉬 함수를 적용하여 추출한 값으로 해쉬 테이블에 엑세스한다.

해쉬 테이블에 엑세스하는 순간 조인을 수행해야 하는 BUILD 테이블의 데이터가 어느 파티션의 어느 서브파티션에 저장되어 있는지 확인할 수 있으므로, 해쉬 테이블에 있는 파티션의 주소를 이용하여 해당 서브파티션으로 이동하여 조인을 수행한다.

(해쉬 조인의 경우 BUILD 테이블로 수행되거나 PROBE 테이블로 수행되어도 한 번만 엑세스하여 조인 수행)

---

SELECT A.COL1, A.COL2, B.COL2, B.COL3
  FROM TABLE_A A, TABLE_B B
 WHERE A.COL3 = B.COL3
   AND A.COL1 >= TRUNC(SYSDATE - 5)
   AND B.COL2 >= TRUNC(SYSDATE - 10)

해쉬 조인의 성능은 아래와 같은 조건에 의해 결정

1. BUILD 테이블의 빠른 해쉬 영역 생성
2. PROBE 테이블에 대한 최적의 엑세스
3. BUILD 테이블에 대한 최적의 엑세스

① BUILD 테이블의 빠른 해쉬 영역 생성

  - 해쉬 영역의 크기는 BUILD 테이블에 의해 추출되는 데이터의 건수에 좌우되며, 해쉬 영역이 크면 모든 데이터가 메모리에 구성되지 못하고 임시 테이블스페이스를 사용하게 되어 디스크 I/O의 증가를 의미

  - BUILD 테이블은 조인에 참여하는 테이블 중 가장 적은 데이터를 추출하는 테이블로 선정해야 한다.

② PROBE 테이블에 대한 최적의 엑세스

  - PROBE 테이블에 빠른 엑세스를 위해 인덱스 구성이 필요하다.

  - TABLE_B의 경우 B.COL2에 인덱스를 구성해야 한다. (대용량 테이블의 경우 전체 스캔 및 병렬 처리 고려 필요)

③ BUILD 테이블에 대한 최적의 엑세스

  - BUILD 테이블에 빠른 엑세스를 위해 인덱스 구성이 필요하다.

  - TABLE_A의 경우 A.COL1에 인덱스를 구성해야 한다. (대용량 테이블의 경우 전체 스캔 및 병렬 처리 고려 필요)

 

해쉬 조인의 경우 해쉬 영역의 크기를 적게 구성하는 테이블이 BUILD 테이블로 지정해서 가장 먼저 엑세스되도록 하는 것이 중요하다.

---

SELECT /*+ ORDERED USE_HASH(A, B) */
       A.COL1, A.COL2, B.COL2, B.COL3
  FROM TABLE_A A, TABLE_B B
 WHERE A.COL3 = B.COL3
   AND A.COL1 >= TRUNC(SYSDATE - 5)
   AND B.COL2 >= TRUNC(SYSDATE - 10)

해쉬 조인의 실행 계획은 위와 같은 방법으로 제어한다.

• ORDERED : FROM 절의 순서대로 테이블에 엑세스 수행
• USE_HASH(A, B) : A 테이블과 B 테이블을 해쉬 죄인 방식으로 조인을 수행
  • A : BUILD 테이블
  • B : PROBE 테이블

#실행 계획 샘플
SELECT STATEMENT
  HASH JOIN
    TABLE ACCESS BY INDEX ROWID OF 'TABLE_A'
      INDEX RANGE SCAN OF 'A.COL1_IDX'
    TABLE ACCESS BY INDEX ROWID OF 'TABLE_B'
      INDEX RANGE SCAN OF 'B.CO2_IDX'

 

---

해쉬 조인의 경우 대용량 데이터 조회뿐 아니라 실시간 조회에서도 사용할 수 있는데, 중첩 루프 조인과 처리 범위가 비슷하고 WHERE 조건에 설정된 조건만으로도 처리 범위가 충분히 감소할 경우 해쉬 조인을 사용하면 효과적이다. 테이블에 엑세스하는 횟수와 두 테이블 조인 시 발생하는 랜덤 엑세스를 감소시킬 수 있는 이점이 존재하기 때문이다.

 

 

[참고] 실행 계획으로 배우는 고성능 데이터베이스 튜닝