[Oracle] 오라클 Hash JOIN, 해시 조인

기본 메커니즘

- 둘 중 작은 집합(Build Input)을 읽어 Hash Area에 해시 테이블을 생성하고, 반대쪽 큰 집합(Probe Input)을 읽어 해시 테이블을 탐색하면서 JOIN하는 방식

- 해시 테이블을 생성할 때나 탐색할 때 모두 해시 함수 사용

- JOIN 과정에서 발생하는 Random 액세스 부하가 없음 (각각의 집합을 읽는 과정에서는 Random 액세스 발생 가능)

- 해시 테이블을 생성하는 비용이 수반되므로 Build Input이 충분히 작아야 효율적

* Hash Area는 PGA 메모리에 할당되는데 Build Input이 hash_area_size를 초과하게 되면 디스크 I/O가 추가로 발생하게되므로 성능이 많이 저하됨

declare

l_bucket number;

begin

for outer in (SELECT DEPT_NO, EMP_NO, RPAD(ENAME, 10) ENAME FROM EMP)

loop    -- outer loop

l_bucket := mod(outer.DEPT_NO, 16);    -- 해시 함수를 적용해 클러스터(=버킷) 확인

for inner in (SELECT DEPT_NO, DNAME, FROM DEPT_HASHTABLE

 WHERE BUCKET = l_bucket    -- 클러스터(=버킷)에서 탐색

AND DEPT_NO = outer.DEPT_NO)

loop    -- inner loop

dbms_output.put_line(outer.EMP_NO ||' : '|| outer.ENAME ||' : '|| inner.DNAME);

end loop;

end loop;

end loop;

- inner loop가 Hash Area에 미리 생성해 둔 해시 테이블을 이용한다는 점만 다를 뿐, 실제 JOIN Operation을 수행하는 과정은 NL JOIN과 동일

- Build Input은 전체범위처리가 불가피, Probe Input은 부분범위처리 가능

- Equal(=) 조건만 사용 가능(적어도 하나의 조건이 '=')

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

- 오라클은 해시 충돌을 최소화 하기 위해 충분히 많은 개수의 버킷을 할당(버킷 하나당 하나의 키 값만 갖는게 이상적)  

- 해시 테이블에 저장할 키 컬럼에 중복값이 많으면 하나의 버킷에 많은 엔트리가 달릴 수밖에 없음(버킷 스캔 시간↑) 

SELECT /*+ use_hash(t o) index(t) index(o) */

~~~~~~~~~~~~~~~~~~

   FROM 주문체결 T, 주문 O

WHERE T.상품번호 = :상품번호

    AND T.체결일자 = :체결일자

    AND O.상품번호 = T.상품번호    -- Hash Key

    AND O.주문일자 = T.체결일자    -- Hash Key

    AND O.주문접수번호 IN (T.매도주문접수번호, T.매수주문접수번호);

-- 해시 버킷 하나에 특정상품의 하루 체결 건수만큼의 키 값이 연결(평균 수천 건)

튜닝 방안

▼

CREATE TABLE 복제테이블 AS SELECT ROWNUM 번호 FROM DAUL CONNECT BY LEVEL <= 100;

SELECT /*+ use_hash(t o) index(o) */

    ~~~~~~~~~~~~~~~~~~

 FROM (SELECT /*+ index(t) index(c) */ ~~~~~~~~~~~~~~~~~~

  , DECODE(C.번호, 1, T.매도주문접수번호, 2, T.매수주문접수번호) 주문접수번호

 FROM 주문체결 T, 복제테이블 X

     WHERE C.번호 <= 2

     ) T, 주문 O

WHERE T.상품번호 = :상품번호

     AND T.체결일자 = :체결일자

     AND O.상품번호 = T.상품번호

     AND O.주문일자 = T.체결일자

     AND O.주문접수번호 = T.주문접수번호    -- Hash Key

Hash JOIN 사용기준

- JOIN 컬럼에 적당한 인덱스가 없어 NL JOIN이 비효율적일 때

- JOIN 컬럼에 인덱스가 있더라도 NL JOIN 드라이빙 집합에서 Inner 쪽 집합으로의 JOIN Access량이 많아 Random Access 부하가 심할 때

- Sort Merge JOIN하기에는 두 테이블이 너무 커 Sort 부하가 심할 때

- 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 JOIN할 때

→ 배치 프로그램, DW, OLAP성 쿼리 등

* 한 쪽 테이블이 Hash Area에 담길 정도로 충분히 작아야 함

* Build Input 해시 키 컬럼에 중복 값이 거의 없어야 함