SQL튜닝이론 - 소트머지조인

SQL튜닝이론 - 소트머지조인

 

조인 컬럼에 인덱스가 없거나, 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때, 옵티마이저는 NL조인 대신 소트머지 조인이나 해시조인을 선택합니다. 소트머지 조인은 해시조인을 사용할 수 없는 상황에서 유용하게 사용됩니다. 소트머지조인은 서버 인스턴스에 할당된 메모리 영역인 PGA (PRIVATE GLOBAL AREA) 를 이용하는 조인방식입니다.

 

소트 머지 조인 특징 요약

조인전 정렬이 먼저 발생하기 때문에 실시간으로 인덱스를 생성해서 조인하는 것과 다름없다.

정렬 후 nl 조인과 같은 방식으로 조인하지만 pga 영역을 이용하기 때문에 속도가 빠르다.

조인 과정에서 인덱스 유무에 영향을 받지 않기 때문에 조인 컬럼이 인덱스에 없는 상황에서 유리하다.

양쪽 테이블에서 조인 대상 레코드를 찾는 과정에서는 인덱스를 이용할 수 있다. 

 

SGA 와 PGA 비교

 

SGA 는 공유 메모리 영역으로 이곳에 저장된 데이터는 여러 서버인스턴스(프로세스)에서 공유할 수 있습니다. 하지만 동시에 엑세스할 수 없기 때문에 엑세스를 직렬화 하여 순차적으로 처리합니다. 직렬화를 위한 LOCK 메커니즘으로 LATCH 가 존재합니다. 데이터 블록과 인덱스 블록을 캐싱하는 DB 버퍼캐시는 SGA의 핵심 구성요소입니다. DB 버퍼에서 캐시데이터를 얻기위해 SGA에 접근해야하기 때문에 버퍼 LOCK이 발생합니다.

 

오라클의 서버 인스턴스는 자신만의 고유 메모리 영역이 있습니다. 이를 PGA 라고 합니다. 인스턴스에 종속적인 고유 데이터를 저장하는 용도입니다. DBMS로 부터 할당받은 PGA 공장이 부족하면 TEMP 테이블스페이스를 이용합니다. PGA 는 PRIVATE 한 메모리 공간이므로 래치 메커니즘이 불필요합니다. 따라서 같은 양의 데이터를 읽더라도 SGA 에서 읽을 때보다 훨씬 빠릅니다.

소트 머지 조인 기본 메커니즘

 

소트 머지조인은 아래 두단계를 거처 진행됩니다.

 

- 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다.
- 머지 단계 : 정렬한 양쪽 집합을 서로 머징한다.

 

use_merge 힌트를 통해 소트 머지 조인으로 유도된 sql의 처리과정은 다음과 같습니다.

 

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

 

조건에 해당하는 사원 데이터를 읽어 조인컬럼인 사원번호 순으로 정렬합니다. 정렬한 결과집합을 PGA에 할당된 SORT AREA에 저장합니다. SORT AREA 가 부족하면 TEMP 테이블스페이스에 저장합니다.

 

SELECT 사원번호, 사원명, 입사일자
FROM 사원
WHERE 입사일자 >= '19960101'
AND 부서코드 = 'Z123'
ORDER BY 사원번호

 

조건에 해당하는 고객 데이터를 읽어 조인컬럼인 관리사원번호 순으로 정렬합니다. 정렬한 결과집합을 PGA에 할당된 SORT AREA에 저장합니다. SORT AREA 가 부족하면 TEMP 테이블스페이스에 저장합니다.

 

SELECT 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
FROM 고객 C
WHERE 최종주문금액 >= 20000
ORDER BY 관리사원번호

 

PGA에 저장된 사원 데이터를 스캔하면서 PGA에 저장된 고객 데이터와 조인합니다.

 

begin
    for outer in (select * from pga_ordered_사원)
    loop
        for inner in (select * from pga_ordered_고객)
            where 관리사원번호 = outer.사원번호
            loop
                dbms_output.put_line(...);
            end loop
    end loop;
end

 

inner table의 조건에 맞는 데이터가 정렬돼있으므로 얻을 수 있는 장점은 다음과 같습니다.

• 조인 대상 레코드가 시작되는 지점을 쉽게 찾을 수 있습니다.
• 조인이 실패하는 레코드를 만나는 순간 조인을 바로 멈출 수 있습니다.

이런 정렬 데이터의 장점으로 인해 조인과정에서 outer data의 레코드 수만큼 full scan 이 발생하지 않습니다.

 

소트 머지 조인이 NL 조인보다 빠른 이유

NL 조인은 기본적으로 인덱스르 이용한 조인 방식입니다. 조인과정에서 발생하는 액세스 만큼 db버퍼를 경우하는 block i/o 가 발생합니다. 즉, 인덱스든 테이블이든, 읽는 블록마다 래치 획득과 캐시버퍼 체인 스캔 과정이 발생합니다. 버퍼 캐시에서 찾지 못한 블럭은 건건이 디스크에서 읽어 들입니다. 인덱스 손익분기점의 한계로 인해 속도면에서 불리합니다.

 

반면 소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA에 저장후 조인하는 방식입니다. PGA는 래치 획득 과정이 없어 속도 면에서 유리합니다. 소트 연산은 대량 데이터 조인에 소트 머지 조인이 유리한 핵심적인 유이 입니다. 소트 머지 조인도 양쪽 테이블로 부터 조인 대상 집합을 읽을 때는 SGA를 경유해야하고 이때 인덱스를 이용합니다. 이 과정에서 생기는 버퍼 캐시 탐색 비용과 랜덤 엑세스 부하는 피할 수 없습니다.

 

소트 머지 조인의 용도

 

해시조인은 소트 머지 조인보다 빠르지만 조인 조건식이 등치 조건이 아닐 때 사용할 수 없다는 단점이 있습니다. 그래서 소트머지 조인은 아래와 같은 상황에 주로 사용됩니다.

 

• 조인 조건식이 등치 조건이 아닌 대량 데이터 조인
• 조인 조건식이 아예없는 조인

 

소트 머지 조인 유도방법

-- 소트머지조인 실행계획

SELECT STATEMENT OPTIMIZER=ALL_ROWS
    MERGE JOIN
        SORT(JOIN)
            TABLE ACCESS (BY INDEX ROWID) OF 사원 TABLE
                INDEX RANGE SCAN OF 사원_X1 INDEX
        SORT(JOIN)
            TABLE ACCESS (BY INDEX ROWID) OF 고객 TABLE
                INDEX RANGE SCAN OF 고객_X1 INDEX                

 

use_merge 힌트로 소트 머지 조인을 유도합니다

 

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