4.2 소트 머지 조인
조인 컬럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스가 효과적이지 않을때,
옵티마이저는 NL조인 대신 소트 머지 조인이나 해시 조인을 사용한다.
해시 조인을 사용할 수 없는 상황에서 대량 데이터를 조인하고자 할 때 유용하다.
4.2.1 SGA / PGA
공유 메모리 영역인 SGA에 캐시된 데이터는 여러 프로세스가 공유 가능하다. 동시에 액세스는 불가능하다.
동시에 액세스 하려는 프로세스 간 액세스 직렬화하기 위해 래치가 존재한다.
각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA 부른다.
할당받은 PGA 공간이 작으면 Temp 테이블 스페이스를 이용한다.
4.2.2 기본 메커니즘
- 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬
- 머지 단계 : 정렬한 양쪽 집합을 서로 머지
select /*+ ordered use_merge(c) */
e.사원번호 ....
c.고객번호 ...
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호
and e.입사일자 >= '19960101'
and e.부서코드 = 'Z123'
and c.최종주문금액 >= 20000
소트 머지 조인은 use_merge 힌트로 유도한다.
사원 테이블 기준으로(ordered) 고객 테이블과 조인할 때 소트 머지 조인 방식 사용(use_merge)
- 사원 조건에 해당되는 데이터를 읽어 조인컬림인 사원번호 순으로 정렬한다. 정렬한 결과 집합은 PGA 영역에 할당된 Sort Area에 저장. 결과값이 크다면 Temp 테이블 스페이스에 저장한다.
select 사원번호 ...
from 사원
..
..
order by 사원번호
- 고객조건에 해당되는 데이터를 읽어 조인 컬럼인 관리사원번호 순으로 정렬. PGA, Temp테이블 스페이스에 저장한다.
select 고객번호 ..., 관리사원번호
from 고객 c
..
..
order by 관리사원번호
- PGA에 저장한 사원 데이터를 스캔하면서 PGA에 저장한 고객 데이터와 조인한다.
1, 2번은 소트단계 3번이 merge 단계이다. 사원 데이터 기준으로 고객 데이터를 매번 Full Scan하지 않는다. 정렬이 되어 있으므로 조인 대상 레코드가 시작되는 지점을 쉽게 찾을 수 있고, 실패하면 바로 멈출 수 있다.
💡 Sort Area 저장한 데이터 자체가 인덱스 역할. 조인 컬럼에 인덱스가 없어도 사용할 수 있는 방식.
NL조인은 대량 데이터 조인할 때 불리하므로 소트머지 조인을 사용할 수 있다.
4.2.3 소트 머지 조인이 빠른 이유
NL 조인은 대량 데이터 조인할 때 성능이 매우 느리다.
소트 머지 조인은 Sort Area에 미리 정렬해 둔 자료구조를 이용한다는 점만 다를 뿐 조인 프로세싱은 NL 조인과 같다.
NL조인은 인덱스를 이용하므로 액세스하는 모든 블록을 랜덤 액세스 방식으로 건건이 DB버퍼캐시를 경유해 읽는다.
소트 머지 조인은 양쪽 테이블로 부터 조인 대상 집합을 읽어 PGA에 저장한 후 조인한다.
PGA는 프로세스만을 위한 독립적인 메모리 공간이므로 래치 획득 과정이 없다.
4.2.4 소트 머지 조인의 주 용도
랜덤 액세스 위주의 NL 조인이 대량 데이터 처리에 한계를 보일때 사용하지만 대부분 해시 조인이 더 빠르기 때문에 예전보다는 덜 사용한다.
하지만 해시 조인은 등치(=) 조건이 아닐 떄 사용할 수 없다는 단점이 있다.
소트 머지 조인은 아래같은 상황에 주로 사용
- 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
- 조인 조건식이 아예 없는 조인
4.2.6 소트 머지 조인 특징 요약
- 양쪽 집합 정량한 다음 PGA 영역에 데이터를 이용해 속도가 빠르다. 소트 부하만 감수하면 건건이 버퍼캐시 경유하는 NL 조인보다 빠르다.
- NL 조인은 조인 컬럼에 대한 인덱스 유무에 따라 크게 영향 받지만, 소트 머지는 영향 X (인덱스가 없는 상황에서 유리하다)
