조인 튜닝 - 소트 머지 조인
조인 칼럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스 효과적이지 않을 때 사용한다.
1. SGA vs PGA
공유 메모리 영역인 SGA에 캐시된 데이터는 여러 프로세스가 공유할 수 있다. 단, 동시에 엑세스 할 수는 없다.
동시에 엑세스하려는 프로세스 간 엑세스를 직렬화하기 위한 Lock 메커니즘으로서 래치가 존재한다.
데이터 블록과 인덱스 블록을 캐싱하는 DB 버퍼캐시는 SGA의 가장 핵심적인 구성요소이며, 블록을 읽으려면 버퍼 Lock도 얻어야 한다.
오라클 서버는 SGA에 공유된 데이터를 읽고 쓰면서, 동시에 자신만의 고유한 메모리 영역을 갖는데, 각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA라고 한다. 할당받은 PGA 공간이 작아 데이터를 모두 저장할 수 없을 대는 Temp 테이블 스페이스를 이용한다.
PGA는 다른 프로세스와 공유하지 않는 독립적인 메모리 공간이므로 래치 메커니즘이 불필요하다. 따라서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.
2. 기본 메커니즘
소트 머지 조인은 아래 두 단계로 진행한다.
1. 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬
2. 머지 단계 : 정렬한 양쪽 집합을 서로 머지 한다.
select /*+ ordered use_merge(c) */
--생략
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호
and e.입사일자 >= '19960101'
and e.부서코드 = 'Z123'
and c.최종주문금액 >= 20000
c라는 별칭의 테이블과 조인할 때 소트 머지 조인 방식을 사용하라는 지시를 내리는 것과 같다.
(1). 기준 테이블 정렬
select 사원번호, 사원명, 입사일자
from 사원
where 입사일자 >= '19960101'
and 부서코드 = 'z123'
order by 사원번호조건에 해당하는 사원 데이터를 읽어 조인컬럼인 사원번호 순으로 정렬한다. 정렬한 결과집합은 PGA 영역에 할당된 Sort Area에 저장한다. (이때 너무 크면 Temp 테이블 스페이스 사용)
(2). 비교 테이블 정렬
select 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
from 고객 c
where 최종주문금액 >= 20000
order by 관리사원번호
똑같이 조건을 통해 결과를 뽑고 정렬한다.
(3). PGA(혹은 테이블스페이스)에 저장한 사원 데이터를 스캔하면서 PGA에 저장한 고객 데이터와 조인한다.
이때 조인 방식은 NL과 크게 다르지 않다.
3. 소트 머지 조인이 빠른 이유
NL조인은 단적으로 말해 인덱스를 이용한 조인 방식이다. 조인 과정에서 엑세스하는 모든 블록을 랜덤 엑세스 방식으로 건건이 DB 버퍼캐시를 경유해서 읽는다. (인덱스던 테이블이던 모든 블록에 래치 획득 및 캐시버퍼 체인 스캔 과정 필요하며 버퍼 캐시에서 찾지 못한 블록은 디스크에서 읽음)
반면 소트 머지 조인은 양쪽 테이블로 조인 대상 집합을 일괄적으로 읽어 PGA에 저장후 조인한다.
이때 PGA는 독립적인 공간이므로 레치 획득 과정이 필요없다.
4. 소트 머지 조인의 주용도
1. 조인 조건식이 등치 조건이 아닌 대량 데이터 조인
2. 조인 조건식이 아예 없는 카테시안 곱 조인
5. 소트 머지 조인 제어하기
소트 머지 조인 실행계획은 위와 같다. 양쪽 테이블을 각각 소트한 후, 실행계획의 위쪽 테이블을 기준으로 실행계획 아래 테이블과 머지 조인한다고 해석하면 된다.
소트 머지 조인 실행계획을 제어할 때 use_merge 힌트를 사용한다.
select /*+ ordered use_merge(c) */
--생략
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호
and e.입사일자 >= '19960101'
and e.부서코드 = 'Z123'
and c.최종주문금액 >= 20000
ordered는 FROM절에 기술한 순서대로 조인하라고 알려주는 힌트이다. (leading(e)를 사용해도 된다)
ordered와 use_merge(c)를 사용했음으로, 정렬된 사원 기준으로 정렬된 고객과 조인하라는 뜻이다.