4.2 소트 머지 조인

4.2.1 SGA vs PGA

• SGA(System Global Area) : 공유 메모리 영역으로 SGA 에 캐시된 데이터는 여러 프로세스가 공유할 수 있다. 하지만 동시 액세스가 되지 않아 Lock 메커니즘인 래치(Latch)가 존재한다.
• PGA(Process/Program/Private Global Area) : 각 오라클 서버 프로세스에 할당된 메모리 영역으로 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용된다. 할당받은 PGA 공간이 작아 데이터를 모두 저장할 수 없는 경우 Temp 테이블스페이스를 이용한다.
  • 다른 프로세스와 공유하지 않는 독립적인 메모리 공간으로 래치 메커니즘이 불필요하다. 따라서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.

4.2.2 기본 메커니즘

소트 머지 조인(Sort Merge Join)
1. 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다.
2. 머지 단계 : 정렬한 양쪽 집합을 서로 머지(Merge)한다.

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

• 위 SQL 은 힌트로 use_merge 를 적어 소트 머지 조인을 명시하였고, ordered 를 명시하여 FROM 절 순서대로 조인이 진행되도록 명시한다.
• 사원 테이블 기준으로(ordered) 고객 테이블과 조인할 때 소트머지 조인 방식을 사용하라(use_merge)고 지시하고 있다.

1번. 사원 테이블 정렬

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

• 현재 조인컬럼은 사원번호 이다. 따라서 정렬 기준은 조인컬럼이 된다.
• 사원 데이터를 사원번호로 정렬한 후 정렬 결과집합을 PGA 영역에 할당된 Sort Area 에 저장한다. 크기가 커 할당 불가능 한 경우 Temp 테이블스페이스에 저장한다.

2번. 고객 테이블 정렬

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

• 조인 컬럼인 사원번호로 고객테이터를 읽어 정렬한다.
• 정령한 결과집합은 PGA 영역에 할당된 Sort Area 에 저장한다. 크기가 커 할당 불가능 한 경우 Temp 테이블스페이스에 저장한다.

3번. 조인

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

• PGA(또는 Temp)에 저장한 사원데이터를 스캔하면서 PGA(또는 Temp) 에 저장한 고객 테이터와 조인한다.
• 3번은 NL 조인과 거의 동일하다

핵심

• 사원데이터를 기준으로 고객 데이터를 매번 Full Scan 하지 않는다.
• 조인 컬럼으로 2개의 테이블이 모두 정렬되어 있기 때문에 조인 대상 레코드가 시작되는 지점과 끝나는 지점을 바로 알 수 있다.
• Sort Area 에 저장한 데이터 자체가 인덱스 역할 이므로 조인 컬럼에 인덱스가 없어도 사용할 수 있다.
• 따라서 NL 조인이 대량 데이터 조인시 불리하지만 소트 머지는 유리한 측면이 존재한다.

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

• NL 조인은 인덱스를 이용한 조인 방식 이다.
  • 조인 과정에서 액세스하느 모든 블록은 랜덤 액세스 방식으로 매번 DB 버퍼캐시를 경유해서 읽는다.
  • 즉 인덱스든 테이블이든, 읽는 모든 블록에 래치 획득 및 캐시버퍼 체인 스캔 과정을 거친다.
  • 버퍼캐시에 없으면 매번 디스크에서 읽어온다.
• 소트 머지 조인은 도인 대상 집합을 일괄적으로 읽어 PGA(또는 Temp) 에 저장한 후 조인한다.
  • PGA 는 독립적 공간으로 래치 획득 과정이 없다.
  • 단 소트 머지 조인도 양쪽 테이블에서 조인 대상 집합을 읽을 떄는 DB 버퍼 캐시를 경유하고 인덱스를 이용하기도 한다.

4.2.4 소트 머지 조인의 주용도

• 해시조인이 더 빠르지만 소트 머지도 여전히 유용하게 쓰인다.
• 주 사용 상황은 다음과 같다
  • 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
  • 조인 조건식이 아예 없는 조인(Cross Join, 카테시안 곱)

Cross Join, 카테시안 곱

• CROSS JOIN은 상호 조인이라고도 불리며, 한 쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인시키는 기능을 한다.
• 그래서, CROSS JOIN의 결과 개수는 두 테이블의 행의 개수를 곱한 개수가 된다.

• UserTable의 첫 행이 BuyTable의 모든 행과 조인되고, 그것을 UserTable의 모든 행이 반복하는 것이다.
• 그러므로 UserTable의 행 갯수 10 과 BuyTable의 행 갯수 14 가 곱해져서 총 개 140 개의 결과가 된다.
• 이러한 CROSS JOIN을 카티션 곱 (Cartesian Product)라고도 한다.
• CROSS JOIN은 대개 테스트로 사용할 대용량의 테이블을 생성할 경우에 사용된다.
• 예를 들어, 각 5 만건과 7 만건의 테이블을 CROSS JOIN 하면 35억건의 데이터를 생성시킬 수 있다.

4.2.5 소트 머지 조인 제어하기

• 아래 실행계획은 양쪽 테이블을 각각 소트한 후 , 위쪽 사원 테이블 기준으로 알쪽 고객 테이블과 머지조인한다 로 해석하면 된다.(소트할 대상을 찾기 위해 테이블 액세스 시 인덱스를 사용하기도 한다.)

-- 소트 후, 위쪽 테이블 기준으로 아래쪽 테이블을 머지 조인
-- 소트할 때 인덱스를 사용해서 인덱스로 표현된 것 뿐이지, 인덱스 없이 스캔했다면 TABLE FULL SCAN으로 나올 수 도 있음
MERGE JOIN
	SORT (JOIN)
		TABLE ACCESS (BY INDEX ROWID) OF '사원' TABLE
			INDEX (RAGNE SCAN) OF '사원_X1' (INDEX)
	SORT (JOIN)
		TABLE ACCESS (BY INDEX ROWID) OF '고객' TABLE
			INDEX (RAGNE SCAN) OF '고객_X1' (INDEX)​