Hash Join과 Sort Merge Join

Hash Join과 Sort Merge Join 처리 순서, Build와 Probe 선택, 힌트 및 아우터 조인 조건

Hash JoinSort Merge Joinuse_hashuse_mergeswap_join_inputsBuildProbeHASH JOIN OUTERPGATemp

#핵심 요약

Nested Loops는 대량 조인에서 디스크 랜덤 I/O 때문에 느려짐
Sort Merge와 Hash Join은 데이터를 PGA에 올려 조인하면서 랜덤 I/O를 줄임

해시 조인 순서: 작은 테이블을 읽어 해시 맵(Build)을 만들고, 큰 테이블을 읽으며(Probe) 해시 맵을 탐색해 조인에 성공한 행을 보냄

한쪽이 현저히 작을 때 해시 조인이 Sort Merge보다 빠른 핵심 이유는 Temp 테이블스페이스를 전혀 쓰지 않을 가능성이 높기 때문임(교재 인용)

해시 조인의 Build 테이블은 swap_join_inputs로, Probe 쪽은 no_swap_join_inputs로 정함

조인 조건이 등치 조건이 아니거나 조인 조건이 없으면 해시 조인을 쓸 수 없음
이때 대량이면 Sort Merge로 유도함

#개념과 동작 원리

항목	설명
두 테이블이 모두 클 때	분할 정복 파티션 단계에서 조인 컬럼에 해시 함수를 적용해 동적으로 나누며 Temp를 쓰고, 조인 단계에서 파티션 짝마다 작은 쪽을 Build로 조인함
Sort Merge가 빠른 이유	PGA에 올려 조인하므로 SGA를 쓰며 래치 경합을 일으킬 필요가 없음
Sort Merge의 버퍼 캐시 사용	양쪽 집합을 읽을 때는 버퍼 캐시를 거치고 인덱스를 쓰기도 함
Sort Merge 조인 순서	선행 테이블 조건으로 작은 데이터 쪽을 먼저 읽어야 함

실습 환경 확인 값: sga_target 2304M, pga_aggregate_target 1G(실습 서버의 SQL*Plus 출력)

#필수 명령어 또는 SQL

#Build 테이블 정하기

SQL*Plus (실습 계정)조회만 함

SELECT /*+ leading(d) use_hash(e) */ e.ename, d.loc
  FROM emp e, dept d WHERE e.deptno = d.deptno;          -- dept가 Build

SELECT /*+ ordered use_hash(d) swap_join_inputs(d) */ e.ename, d.loc
  FROM emp e, dept d
 WHERE e.deptno = d.deptno AND e.job = 'SALESMAN';       -- emp를 먼저 읽지만 dept를 Build로

결과 해석: ordered use_hash(d)만 주면 먼저 읽는 emp가 Build가 됨
swap_join_inputs(d)를 더하면 dept가 Build로 바뀜

#세 테이블 해시 조인

SQL*Plus (실습 계정)객체 또는 설정을 변경함

CREATE TABLE bonus AS SELECT job, SUM(sal) comm2 FROM emp GROUP BY job;

SQL*Plus (실습 계정)조회만 함

SELECT /*+ leading(d, e, b) use_hash(e) use_hash(b) swap_join_inputs(b) */
       e.ename, e.sal, d.loc, b.comm2
  FROM emp e, dept d, bonus b
 WHERE e.deptno = d.deptno AND e.job = b.job;

결과 해석:

실습 결과(텍스트): 위쪽 HASH JOIN의 첫 자식이 BONUS(Build), 둘째 자식이 DEPT와 EMP의 HASH JOIN임
전체 Buffers 17
연습 문제 5번(bonus가 Build이고 안쪽이 EMP → DEPT인 계획)의 답은 기재 없음

#아우터 조인의 해시 테이블

SQL*Plus (실습 계정)조회만 함

SELECT /*+ leading(d) use_hash(e) */ e.ename, d.loc
  FROM emp e, dept d WHERE e.deptno = d.deptno(+);       -- 결과: HASH JOIN OUTER, EMP가 첫 자식

SELECT /*+ leading(d) use_hash(e) swap_join_inputs(d) */ e.ename, d.loc
  FROM emp e, dept d WHERE e.deptno = d.deptno(+);       -- dept를 해시 테이블로

결과 해석: 실습 첫 실행계획은 leading(d)를 줬는데도 아우터 기준 테이블 EMP가 먼저(해시 테이블로) 나왔음
아우터 조인에서 leading이 무시되는 이유는 기재 없음

#Sort Merge Join

SQL*Plus (실습 계정)조회만 함

SELECT /*+ leading(d e) use_merge(e) */ e.ename, d.loc, e.deptno
  FROM emp e, dept d WHERE e.deptno = d.deptno;

-- 해시 조인이 안 되는 비등치 조인을 대량으로 할 때
SELECT /*+ leading(s, e) use_merge(e) */ e.ename, e.sal, s.grade
  FROM emp e, salgrade s
 WHERE e.sal BETWEEN s.losal AND s.hisal;

결과 해석:

실습에서는 같은 비등치 조인에 use_hash(e)를 줘서 해시 조인이 되지 않는 것을 먼저 확인하게 했음
조건으로 읽을 양이 많으면 Sort Merge 양쪽 입력에도 인덱스를 쓸 수 있음

SQL*Plus (실습 계정)조회만 함

SELECT /*+ leading(d e) use_merge(e) index(d, dept_loc) index(e, emp_job) */
       e.ename, d.loc, e.deptno
  FROM emp e, dept d
 WHERE e.deptno = d.deptno AND e.job = 'SALESMAN' AND d.loc = 'CHICAGO';

결과 해석:

정렬 여부 비교: 실습에서는 같은 SQL을 use_merge와 use_hash로 각각 실행해 Sort Merge만 정렬(SORT JOIN)을 일으키는지 확인하게 했음
퀴즈 답: 예약 기준으로 회원과 Sort Merge를 유도하는 힌트는 ordered use_merge(m), SQL Server는 option (force order, merge join)