소트 머지 조인
조인 컬럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때, 옵티마이저는 NL 조인 대신 소트 머지 조인이나 해시 조인을 선택한다. 해시 조인의 등장으로 소트 머지 조인의 쓰임새가 예전만 못하지만, 해시 조인을 사용할 수 없는 상황에서 대량 데이터를 조인하고자 할 때 여전히 유용하다.
힌트 : use_merge(innerTable)
기본 메커니즘
1. 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다.
2. 머지 단계 : 정렬한 양쪽 집합을 서로 머지한다.
1. PGA의 Sort Area영역에 조건의 결과 Outer Table을 조인컬럼 순으로 정렬한다. PGA에 담을 수 없을 정도로 크면 Temp 테이블스페이스에 저장한다.
2. PGA의 Sort Area영역에 조건의 결과 Inner Table을 조인컬럼 순으로 정렬한다. PGA에 담을 수 없을 정도로 크면 Temp 테이블스페이스에 저장한다.
3. PGA에 저장한 Outer Table 테이터를 스캔하면서 PGA에 저장한 Inner Table와 조인한다. 조인 과정을 PL/SQL 코드로 표현하면 NL 조인과 동일하다(중첩 For문)
소트머지 조인이 빠른 이유
NL 조인의 치명적인 단점은 대량 데이터 조인할 때 성능이 매우 느리다는 데 있다. 소트 머지 조인과 해시 조인이 개발된 이유이다.
소트머지 조인은 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA에 저장후 조인한다. PGA는 프로세스만을 위한 독립적인 메모리 공간이므로 데이터를 읽을 때 래치 획득 과정이 없다. 소트머지 조인이 대량 데이터 조인에 유리한 이유다. 소트 머지 조인도 양쪽 테이블로부터 조인 대상 집합을 읽을 때는 DB버퍼캐시를 경유한다. 이때 인덱스를 이용하기도 한다. 이 과정에서 생기는 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 소트 머지 조인도 피할 수 없다.
소트머지 조인의 주용도
대부분의 경우 해시 조인이 더 빠르지만 해시 조인은 조인 조건식이 등치 조건이 아닐 때 사용할 수 없다는 단점이 있다.
1. 조인 조건식이 등치 조건이 아닌 대량 데이터 조인
2. 조인 조건식이 아예 없는 조인(카테시안 곱)