친절한 SQL 튜닝_4장.조인 튜닝_2

 

조인 튜닝

 

4.2 소트 머지 조인

 

4.2.1 SGA vs. PGA

 

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

 

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

 

 

 

4.2.2 기본 메커니즘

 

소트 머지 조인(Sort Merge Join)은 이름의 의미하는 것처럼 아래 두 단계로 진행하다.

 

1.소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다.

 

2.머지 단계 : 정렬한 양쪽 집합을 서로 머지(Merge)한다.

 

 

 

NL 조인에서 사용했던 아래 SQL로 소트 머지 조인 과정을 설명해 보자. 소트 머지 조인은 use_merge 힌트로 유도한다. 아래 SQL에 사용한 힌트는, 사원 테이블 기준으로(ordered) 고객 테이블과 조인할 때 소트 머지 조인 방식을 사용하라(use_merge)고 지시하고 있다.

 

 

1 2 3 4 5 6 7 8

SELECT /*+ ordered use_merge(c) */        e.사원번호, e.사원명, e.입사일자      , c.고객번호, c.고객명, c.전화번호, c.최종주문금액   FROM 사원 e, 고객 c  WHERE c.관리사원번호 = e.사원번호    AND e.입사일자 >= ‘19960101’    AND e.부서코드 = ‘Z123’    AND c.최종주문금액 >= 2000

cs

 

 

 

 

1.아래 조건에 해당하는 사원 데이터를 읽어 조인컬럼인 사원번호 순으로 정렬한다. 정렬한 결과집합은 PGA 영역에 할당된 Sort Area에 저장한다. 정렬한 결과집합이 PGA에 담을 수 없을 정도로 크면, Temp 테이블스페스에 저장한다.

 

 

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

 

 

 

2.아래 조건에 해당하는 고객 데이터를 읽어 조인컬럼인 관리사원번호 순으로 정렬한다. 정렬한 결과집합은 PGA 영역에 할당된 Sort Area에 저장한다. 정렬한 결과집합이 PGA에 담을 수 없을 정도로 크면, Temp 테이블스페스에 저장한다.

 

 

1 2 3 4

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

cs

 

 

 

3.PGA(또는 Temp 테이블스페이스)에 저장한 사원 데이터를 스캔하면서 PGA(또는 Temp 테이블 스페이스)에 저장한 고객 데이터와 조인한다. 조인하는 과정을 PL/SQL 코드로 표현하면 아래와 같다.

 

 

1 2 3 4 5 6 7 8 9

begin     for outer in (select * from PGA_정렬된_사원)     loop -- outer 루프         for inner in (select * from PGA_정렬된_고객 where 관리사원번호 = outer.사원번호)         loop -- inner 루프             dbms_output.put_line( … );         end loop;     end loop; end Colored by Color Scripter

cs

 

 

1번과 2번이 소트 단계, 3번이 머지 단계다. 실제 조인 오퍼레이션을 수행하는 3번 머지 단계는 NL 조인과 다르지 않음을 위 Pseudo 코드를 통해 알 수 있다.

 

사원 데이터를 기준으로 고객 데이터를 매번 Full Scan 하지 않는다는 사실이다. 고객 데이터가 정렬돼 있으므로 조인 대상 레코드가 시작되는 지점을 쉽게 찾을 수 있고, 조인에 실패하는 레코드를 만나는 순간 바로 멈출 수 있다.

 

Sort Area에 저장한 데이터 자체가 인덱스 역할을 하므로 소트 머지 조인은 조인 컬럼에 인덱스가 없어도 사용할 수 있는 조인 방식이다. 조인 컬럼에 인덱스가 있어도 NL 조인은 대량 데이터 조인할 때 불리하므로 소트 머지 조인을 사용할 수 있다.                

 

 

 

4.2.3 소트 머지 조인이 빠른 이유

 

NL 조인은 모든 DBMS가 공통으로 제공하는 가장 전통적인 조인 방식이다. 그런 NL 조인의 치명적인 단점은 대량 데이터 조인할 때 성능이 매우 느리다는 데 있다. 소트 머지 조인과 해시 조인이 개발된 이유다.

 

소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합(조인 조건 이외 필터 조건을 만족하는 집합)을 ‘일괄적으로’ 읽어 PGA(또는 Temp 테이블스페이스)에 저장한 후 조인한다. PGA는 프로세스만 위한 독립적인 메모리 공간이므로 데이터를 읽을 때 래치 획득 과정이 없다. 소트 머지 조인이 대량 데이터 조인에 유리한 이유다.

 

조인 전에 양쪽 집합에 대한 소트 연산을 추가로 수행하므로 NL 조인보다 더 느리지 않을까 싶지만, 이것이 오히려 소트 머지 조인을 대량 데이터 조인에 유리하게 만든 핵심 요인인 셈이다.

 

소트 머지 조인도 양쪽 테이블로부터 조인 대상 집합을 읽을 때는 DB 버퍼캐시를 경유한다. 이때 인덱스를 이용하기도 한다. 이 과정에서 생기는 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 소트 머지 조인도 피할 수 없다.

 

 

 

4.2.4 소트 머지 조인의 주용도

 

- 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인

 

- 조인 조건식이 아예 없는 조인(Cross Join, 카테시안 곱)

 

 

 

4.2.5. 소트 머지 조인 제어하기

 

SELECT /*+ ordered use_merge(c) */
       e.사원번호, e.사원명, e.입사일자
     , c.고객번호, c.고객명, c.전화번호, c.최종주문금액
  FROM 사원 e, 고객 c
 WHERE c.관리사원번호 = e.사원번호
   AND e.입사일자 >= ‘19960101’
   AND e.부서코드 = ‘Z123’
   AND c.최종주문금액 >= 2000

 

ordered는 FROM 절에 기술한 순서대로 조인하라고 옵티마이저에 지시하는 힌트다. ordered 대신 leading(e) 힌트를 사용해도 된다. use_merge는 소트 머지 방식으로 조인하라고 지시하는 힌트다. 위에서는 ordered와 use_merge(c) 힌트를 같이 사용했으므로 양쪽 테이블을 조인 컬럼 순으로 각각 정렬한 후 ‘정렬된 사원’ 기준으로 ‘정렬된 고객’과 조인하라는 뜻으로 해석하면 된다.

 

 

 

4.2.6 소트 머지 조인 특징 요약

 

소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다. 양쪽 집합을 정렬한 다음에는 NL 조인과 같은 방식으로 진행하지만, PGA 영역에 저장한 데이터를 이용하기 때문에 빠르다고 설명했다. 따라서 소트 부하만 감수한다면, 건건이 버퍼캐시를 경유하는 NL 조인보다 빠르다.

 

NL 조인은 조인 컬럼에 대한 인덱스 유무에 크게 영향을 받지만, 소트 머지 조인은 영향을 받지 않는다. 양쪽 집합을 개별적으로 읽고 나서 조인을 시작한다는 특징도 있다. 따라서 조인 컬럼에 인덱스가 없는 상황에서 두 테이블을 각각 읽어 조인 대상 집합을 줄일 수 있을 때 아주 유리하다.

 

스캔 위주의 액세스 방식을 사용한다는 점도 중요한 특징이다. 하지만 모든 처리가 스캔 방식으로 이루어지진 않는다. 양쪽 소스 집합으로부터 조인 대상 레코드를 찾는 데 인덱스를 이용할 수 있고, 그때는 랜덤 액세스가 일어난다. 이는 해시 조인도 마찬가지다.

 

 

 

출처 : 친절한 SQL 튜닝 - 조시형 지음