SORT MERGE JOIN

SORT MERGE JOIN 은 연결 고리에 이상이 있는 경우 대용량의 자료를 조인할 경우 인덱스의 치명적인 단점인 랜덤 액세스와 오버헤드가 Nested Loop Join 의 장점을 넘어서 버리는 경우에 적절하게 사용할 수 있는 조인 방법

-> Join 조건에 해당되는 컬럼에 인덱스가 없을 경우 Nested Loop에서는 Full 스캔하게 되어 엄청난 오버헤드를 가지게 된다. 이점을 해결하기 위해 단 두번의 Scan으로 처리가 가능한 Sort Merge Join이나 Hash Join이 생겨나게 된것

SORT MERGE JOIN은 조인이 되는 각각의 테이블 자료를 스캔방식으로 읽어들이거나 인덱스를 사용하여 메모리로 읽어들입니다.

읽혀진 두 테이블의 조인 집합은 연결 고리 칼럼에 대하여 각각 정렬을 수행한 후 조인 작업이 수행됨. 정렬을 수행하기 위하여 SORT_AREA_SIZE 크기 만큼 메모리를 할당 받아 사용하게 되며, 메모리가 부족하다면 temporary tablespace를 이용하여 정렬을 수행하게 된다.

튜닝포인트
- SORT를 얼마나 효과적으로 하느냐가 튜닝포인트다.
- SORT_AREA_SIZE를 적절한 크기로 지정하는 것이 튜닝 포인트가 된다.

SELECT /*+USER_MERGE(A B)*/ A.COLOR, B.SIZE
FROM TABLE_A A, TABLE_B B
WHERE a.joinkey_a = b.joinkey_b    --  joinkey에 대한 인덱스가 테이블 둘 모두 다 없음
AND a.color = 'RED'                      -- 인덱스 있음
AND b.size = 'MED'                      -- 인덱스 없음


실행계획

SELECT
 MERGE JOIN
  SORT(JOIN)  ==> Join Key를 중심으로 Sorting을 시도한다. 
   TABLE ACCESS (By Index Rowid) TABLE_A
    Index(Range)...
  SORT(JOIN) ==> Join Key를 중심으로 Sorting 을 시도한다.
    TABLE (FULL) TABLE_B

HINT설명
USE_MERGE(TABLE1, TABLE2) : SORT MERGE JOIN
옵티마이저가 SORT MERGE JOIN을 사용하도록 한다.
먼저 각각의 TABLE의 처리 범위를 스캔하여 SORT한 후, 서로 MERGE하면서 JOIN하는 방식

SORT MERGE JOIN의 장단점

단점 
두 결과 집합의 크기가 많이 차이나는 경우에는 SORT MERGE JOIN이 비효율적이다.
어느 한 쪽이라도 정렬 작업이 종료되지 않으면 조인이 시작될 수 없으므로 두 테이블 조인 집합의 크기가 많이 차이가 난다면 한쪽에 '대기' 상태가 발생하여 비효율적으로 처리가 된다. 이렇게 크기가 비슷하지 않은 집합의 조인을 위해서 HASH 조인을 사용할 수 있다.