02. 소트 머지 조인

정의


소트 머지 조인(Sort Merge Join)은 두 테이블을 각각 조인 컬럼 기준으로 정렬한 다음, 두 집합을 머지(Merge)하면서 조인을 수행함

동작


1.소트 단계: 양쪽 집합을 조인 컬럼 기준으로 정렬함
2.머지 단계: 정렬된 양쪽 집합을 서로 머지(Merge)함

사용법


select /*+ ordered use_merge(t2) */ *
from t1, t2
where t1.c1 = t2.c1

NL 조인을 효과적으로 수행하려면 조인 컬럼에 인덱스가 필요함

만약 적절한 인덱스가 없다면 Inner 테이블을 탐색할 때마다 반복적으로 Full Scan을 수행하므로 매우 비효율적임
그럴 때 옵티마이저는 소트 머지 조인이나 해시 조인을 고려함

소트 머지 조인은 outer 루프와 inner 루프가 Sort Area 에 미리 정렬해 둔 자료구조를 이용한다는 점만 다를 뿐,
실제 조인 오퍼레이션을 수행하는 과정은 NL 조인과 다르지 않음. NL 조인과 마찬가지로 outer 조인할 때 순서가 고정됨

Sort Area는 PGA 영역에 할당되므로 SGA를 경유해 인덱스와 테이블을 액세스할 때보다 훨씬 빠름
PGA는 프로세스만을 위한 독립적인 메모리 공간이어서 데이터를 읽을 때 래치 획득 과정이 없기 때문임

후행 테이블이 정렬돼 있기 때문에 조인에 실패하는 레코드를 만나는 순간 멈출 수 있음
정렬된 후행 테이블에서 스캔 시작점을 찾으려고 매번 탐색하지 않아도 됨

NL 조인 시 버펴 캐시에서 같은 블록을 반복적으로 액세스할 때 발생하는 비효율을 없에려고 Buffer Pinning 기능이 점점 확대 적용됨

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

스캔 위주의 액세스 방식을 사용하는 특징이 있지만, 모든 처리가 스캔 방식으로 이루어지는 것음 아님

양쪽 소스 집합에서 정렬 대상 레코드를 찾는 작업만큼은 인덱스를 이용해 Random 액세스 방식으로 처리될 수 있고,
그때 발생한 Random 액세스량이 많다면 소트 머지 조인의 이점이 사라질 수도 있음. 이는 해시 조인도 마찬가지임

소트 머지 조인의 유용


(1) First 테이블에 소트 연산을 대체할 인덱스가 있을 때 

- 해시 조인과 마찬가지로, 한쪽 집합(Second 테이블)은 전체범위를 처리하고 
  다른 한쪽(First 테이블)을 일부만 읽고 멈추도록 할 수 있음

- First 테이블 조인 컬럼에 인덱스가 있을 때 가능

- OLTP성 업무에서 소량의 테이블과 대량의 테이블을 조인할 때, 소트 머지 조인을 유용하게 사용할 수 있음

- 소트 머지 조인에서 인덱스를 이용해 소트 연산을 대체할 수 있는 대상은 First 테이블에만 국한됨

- Second 테이블 조인 컬럼에 대한 인덱스를 이용함에도 Sort Join 오퍼레이션이 나타남.

- First 테이블은 이미 정렬된 인덱스를 사용할 것이므로 그대로 두고, 먼저 Second 집합 테이블을 읽어 정렬한 결과를 Sort Area에 담음

- 조인 연산을 진행할 때는 First 집합 테이블의 인덱스부터 읽기 시작함

- 소트 머지 조인도 부분적으로 부분범위처리가 가능함
  Second 테이블은 항상 정렬을 수행하므로 전체범위처리가 불가피하지만, First 테이블만큼은 중간에 읽다가 멈출 수 있음

- Second 테이블은 조인 컬럼에 인덱스가 있더라도 정렬을 수행함



(2) 조인할 First 집합이 이미 정렬돼 있을 때

- First 집합이 정렬돼 있을 때만 소트 연산이 생략됨, 
  Second 집합은 설사 정렬돼 있더라도 Sort Join 오퍼레이션을 수행함


(3) 조인 조건식이 등치(=) 조건이 아닐 때

- 해시 조인은 조인 조건식이 등치(=) 조건일 때만 사용할 수 있지만 
  소트 머지 조인은 등치 조건이 아닐 때도(between, <, <=, >, >=) 사용될 수 있음