[Oracle] 오라클 Sort Merge JOIN, 소트 머지 조인

기본 메커니즘

- Sort (양쪽 집합을 JOIN 컬럼 기준으로 정렬) → Merge (정렬된 양쪽 집합을 서로 Merge)

SELECT /* ordered use_merge(d) */ E.EMP_NO, E.ENAME, D.DNAME

  FROM EMP E, DEPT D

WHERE D.DEPT_NO = E.DEPT_NO;

▼

begin

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

loop    -- outer loop

for inner in (SELECT DNAME FROM SORTED_DEPT WHERE DEPT_NO = outer.DEPT_NO)

loop    -- inner loop

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

end loop;

end loop;

end;

- outer loop와 inner loop가 Sort Area에 미리 정렬해 둔 자료구조를 이용한다는 점만 다를 뿐, 실제 JOIN Operation을 수행하는 과정은 NL JOIN과 동일

* Sort Area는 PGA 영역에 할당되므로 SGA를 경유해 인덱스와 테이블을 액세스할 때보다 훨씬 빠름(latch 경합 X)

SELECT /*+ ordered use_merge(e) */ D.DEPT_NO, D.DNAME, E.EMP_NO, E.ENAME

  FROM DEPT D, EMP E

WHERE D.DEPT_NO = E.DEPT_NO

▼

- Inner Table(emp)이 정렬돼 있기 때문에 JOIN에 실패하는 레코드를 만나는 순간 멈춤

- 스캔 시작점을 찾기 위해 매번 탐색하지 않아도 됨(①→②→③ 순으로 바톤 패스)

- JOIN 컬럼에 인덱스가 없는 상황에서 두 테이블을 독립적으로 읽어 JOIN 대상 집합을 줄일 수 있을 때 매우 유리

Sort Merge JOIN의 활용

- First(Outer) 테이블에 Sort 연산을 대체할 인덱스가 있을 때

CREATE INDEX DEPT_IDX ON DEPT(LOC, DEPT_NO);

SELECT /*+ ordered use_merge(e) */ *

  FROM DEPT D, EMP E

WHERE D.DEPT_NO = E.DEPT_NO

    AND D.LOC = 'CHICAGO'

    AND E.JOP = 'SALESMAN'

ORDER BY E.DEPT_NO;

-- LOC = 'CHICAGO' 조건으로 스캔하면서 얻어진 결과집합은 이미 DEPT_NO 순으로 정렬(Sort 연산 필요 X)

-- 인덱스를 이용해 Sort 연산을 대체할 수 있는 대상은 First 테이블에만 국한됨

SELECT /*+ ordered use_merge(e) index(d dept_pk) */

      D.DNAME, E.EMP_NO, E.ENAME

  FROM DEPT D, EMP E

WHERE E.DEPT_NO = D.DEPT_NO; 

▼

- 부분범위처리 가능

: Second(Inner) 테이블은 항상 정렬을 수행하므로 전체범위처리가 불가피하지만 First(Outer) 테이블은 중간에 읽다가 멈출 수 있음

∴ 데이터가 적은 테이블이 Second 테이블이 되는 것이 유리

- JOIN할 First 집합이 이미 정렬되어 있을 때

SELECT /*+ ordered use_merge(d) */ D.DEPT_NO, D.DNAME, E.AVG_SAL

  FROM (SELECT DEPT_NO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPT_NO ORDER BY DEPT_NO) E

    , DEPT D

WHERE E.DEPT_NO = D.DETP_NO;

- JOIN 조건식이 Equal(=) 조건이 아닐 때

SELECT /*+ ordered use_merge(e) */ D.DEPT_NO, D.DNAME, E.EMP_NO, E.ENAME

  FROM DEPT D, EMP E

WHERE D.DEPT_NO <= E.DEPT_NO;

▼

* 부등호 방향을 아래와 같이 바꾸면 오라클은  Sort JOIN을 위해 두 집합을 내림차순으로 정렬

SELECT /*+ ordered use_merge(e) */ D.DEPT_NO, D.DNAME, E.EMP_NO, E.ENAME

  FROM DEPT D, EMP E

WHERE D.DEPT_NO >= E.DEPT_NO;

-- 쿼리 수행 시 DEPT_NO가 큰 값부터 출력

▼