4.2 소트 머지 조인

1. 조인 컬럼에 인덱스가 없을 때
2. 대량 데이터 조인이어서 인덱스가 효과가 없을 때

옵티마이저는 NL 조인 대신 소트 머지 조인이나 해시 조인을 선택한다. 해시 조인을 사용할 수 없는 상황에서 대량 데이터를 조인하고자 할 때 소트 머지 조인을 사용한다.

---

4.2.1 SGA vs. PGA

공유 메모리 영역인 SGA에 캐시된 데이터는 여러 프로세스가 공유할 수 있지만, 동시에 액세스는 불가능하다. 따라서 액세스를 직렬화하기 위한 Lock 메커니즘으로 래치(Latch)가 존재한다. 데이터 블록과 인덱스 블록을 캐싱하는 DB 버퍼캐시는 SGA의 가장 핵심적인 구성요소이먀, 여기서 블록을 읽으려면 버퍼 Lock도 얻어야 한다. (1.3.8 https://gyujingyujin.tistory.com/13?category=1262044 참고)

오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서, 동시에 자신만의 고유 메모리 영역을 갖는다.
PGA(Process/Program/Private Global Area)는 다른 프로세스와 공유하지 않는 독립적인 메모리 공간이므로 래치 메커니즘이 불필요 하다. 따라서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다. PGA 공간이 작아 데이터를 모두 적재할 수 없을 때는 Temp 테이블스페이스를 이용한다.

---

4.2.2 기본 메커니즘

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

select /*+ ordered use_merge(c) */ *
from    사원 e, 고객 c
...

--사원 테이블 기준으로(ordered) 고객 테이블과 소트 머지 조인.

1) 각 테이블은 조건에 맞게 발췌 되고 조인컬럼을 순으로 정렬된다. 정렬된 결과집합은 PGA 영역에 할당된 Sort Area에 저장한다.(결과집합이 못 담을 정도로 크면 Temp 테이블 스페이스에 저장한다.
2) PGA에 저장한 '사원' 데이터를 스캔하면서 PGA에 저장된 '고객'데이터와 조인한다.

실제 NL 조인과 다르지 않지만 주목할 점은, 매번 Full Scan을 하지 않는다. 는 사실이다.

Sort Area에 저장한 데이터 자체가 인덱스 역할을 하므로 소트 머지 조인은 조인 컬럼에 인덱스가 없어도 사용할 수 있다.(대량 데이터 조인 시 불리한 NL조인을 대신해서도 사용 가능.)

---

4.2.3 소트 머지 조인이 빠른 이유

NL 조인은 인덱스를 이용한 조인 방식이다. 조인 과정에서 액세스를하는 모든 블록을 랜덤 액세스 방식으로 '건건이' DB 버퍼캐시를 경유해서 읽는다. 즉, 인덱스든 테이블이든, 읽는 모든 블록에 래치 획득 및 캐시버퍼 체인 스캔 과정을 거친다. 버퍼캐시에서 찾지 못한 블록은 '건건이' 디스크에서 읽어 들인다. 인덱스를 이용하기 때문에 인덱스 손익분기점 한계가 그대로 드러난다.
반면, 소트 머지 조인은 양쪽 테이블에서 '일괄적으로' 읽어 PGA(또는 Temp 테이블스페이스)에 저장한 후 조인한다. PGA는 프로세스만을 위한 독립적인 메모리 공간이므로 데이터를 읽을 때 래치 획득 과정이 없다. 따라서 대량 데이터 조인에 유리하다.
소트 머지 조인도 양쪽 테이블로부터 조인 대상 집합을 읽을 때는 DB 버퍼캐시를 경유한다. 이때 인덱스를 이용하기도 한다.

---

4.2.4 소트 머지 조인의 주용도

대량 데이터 시 대부분은 해시 조인이 소트 머지 조인보다 더 빠르다. 하지만 해시 조인은 조인 조건식이 등치(=)가 아닐 때 사용할 수 없다. 그래서 소트 머지 조인은 아래와 같은 상황에 주로 사용된다.

• 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
• 조인 조건식이 아예 없는 조인(Cross Join, 카테시안 곱)

---

4.2.5 소트 머지 조인 제어하기

아래는 소트 머지 조인 실행계획이다.

Execution Plan
-------------------------------------------------------------------------------------
0        SELECT STATEMENT Optimizer=ALL_ROWS
1    0        MERGE JOIN
2    1            SORT (JOIN)
3    2                TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
4    3                    INDEX (RANGE SCAN) OF '사원_X1' (INDEX)
5    1            SORT (JOIN)
6    5                TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
7    6                    INDEX (RANGE SCAN) OF '고객_X1' (INDEX)

select /*+ ordered use_merge(c) */ *
from    사원 e, 고객 c
...

use_merge 힌트를 사용해서 소트 머지 조인 실행계획을 제어한다. ordered는 from 절에 기술한 순서대로 조인하라고 옵티마이저에 지시하는 힌트다.

---

4.2.6 소트 머지 조인 특징 요약

• 소트 부하만 감수한다면 건건이 버퍼캐시를 경유하는 NL 조인보다 빠르다.
• 인덱스에 영향을 받지 않는다.
  • 양쪽 집합을 개별적으로 읽고 나서 조인을 시작한다. 따라서 조인 컬럼에 인덱스가 없는 상황에서도 사용 가능하다.
• 대부분 스캔 위주의 액세스 방식을 사용한다.
  • 양쪽 소스 집합으로부터 조인 대상 레코드를 찾는 데 인덱스를 이용할 수 있고, 그때는 랜덤 액세스가 일어난다.(해시 조인도 마찬가지)