소트머지조인

• 언제 사용할까?
  조인컬럼에 인덱스가 없을때, 대량데이터 조인이어서 인덱스가 효과적이지 않을 때, 옵티마이저는 NL조인 대신 소트머지조인이나 해시조인을 선택한다.

• SGA vs PGA?
  오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서, 동시에 자신만의 고유 메모리 영역을 갖는다. 각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA(Private Global Area)라고 부르며, 프로세스에 종속적인 고유 데이터를 저장하는 용도로 한다. 할당 받은 PGA공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp테이블스페이스를 이용한다.

• PGA는 다른 프로세스와 공유하지 않는 독립적인 메모리 공간이므로 래치 메커니즘이 필요없고, 따라서 같은 양의 데이터를 읽더라도 SGA(Shared Global Area)버퍼캐시에서 읽을 때보다 훨씬 빠르다.

• 소트머지조인 수행 단계는?
  소트단계(양쪽 집합을 조인 컬럼 기준으로 정렬한다.) -> 머지단계(정렬한 양쪽 집합을 서로 merge한다.
  

• use_merge 힌트로 유도한다.

• sql수행과정

select /*+ ordered use_merge(c)*/
       e.사원번호, e.사원명, c.고객명
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호
  and e.입사일자 >= '19960101'
  and e.부서코드 = 'Z123'
  and c.최종주문금액 >= 20000

1. 사원 테이블의 where 조건절을 만족하는 데이터를 읽어 조인키인 사원번호를 기준으로 정렬한다.
   정렬한 결과집합은 PGA영역에 할당된 Sort Area에 저장하는데 다 저장못하면 Temp 테이블스페이스에 저장한다.
2. 고객 테이블의 where 조건절을 만족하는 데이터를 읽어 조인키인 관리사원번호를 기준으로 정렬한다. 정렬한 결과집합은 PGA영역에 할당된 Sort Area에 저장하는데 다 저장못하면 Temp 테이블스페이스에 저장한다.
3. PGA(또는 Temp테이블스페이스)에 저장한 사원데이터를 스캔하면서 PGA(또는 Temp테이블스페이스)애 저장한 고객데이터와 조인한다. 이때 사원데이터를 기준으로 고객데이터를 매번 Full Scan하지 않는다. -> 고객 데이터가 정렬돼있으므로 조인대상레코드가 시작되는 지점을 쉽게찾고, 조인에 실패하는 데이터를 만나는 순간 멈출수 있기때문임.
   즉, Sort Area에 저장한 데이터 자체가 인덱스 역할을 하므로, 소트머지조인은 조인컬럼에 인덱스가 없어도 사용할 수 있는 조인방식이다.

• 그럼 왜 대량데이터를 조인할때 NL조인보다 소트머지 조인이 빠를까?
  NL조인은 조인과정에서 액세스하는 모든블록을 랜덤액세스방식으로 건건이 DB버퍼캐시를 경유해서 읽는다. 즉 인덱스든 테이블이든 읽는 모든 블록에 래치획득 및 버퍼캐시 체인 스캔과정을 거친다. 버퍼캐시에서 찾지 못한 블록은 건건이 디스크에서 읽어들인다.
  소트머지조인은 양쪽테이블로부터 조인대상집합을 일괄적으로 읽어 PGA에 저장한 후 조인한다. PGA는 프로세스만을 위한 독립적인 메모리공간이므로 데이터를 읽을 때 래치 획득 과정이 없다. 소트머지 조인이 대량 데이터 조인에 유리한 이유다.

• 언제 소트머지조인을 주로 사용할까?
  소트머지조인보다 해시조인이 대부분 더 빨라서 대량건이면 해시조인을 이용한다. 하지만 해시조인은 조인조건식이 =조건이 아니면 사용하지 못한다. 따라서 대량건인데 조인조건식이 =이 아닐때 사용할 수 있다.

• 소트머지조인 특징 요약
  소트머지조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다.
  양쪽 집합을 개별적으로 읽고 정렬한이후에 조인을 시작한다. 따라서 조인컬럼에 인덱스가 없는 상황에서 두 테이블을 각각 읽어 조인 대상집합을 줄일 수 있을때 아주유리하다.