4.2 소트 머지 조인

• 조인 컬럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때 옵티마이저는 소트머지 조인이나 해시 조인을 선택한다
• use_merge : 소트 머지 방식으로 조인하라고 지시하는 힌트
• 소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다.
• 모든 처리가 스캔 방식으로 이루어지진 않으며, 양쪽 집합으로부터 조인 대상 레코드를 찾는데 인덱스를 이용할 수도 있고, 그 때는 랜덤 액세스가 일어난다.

---

SGA vs PGA

• SGA는 공유 메모리 영역으로, 여러 프로세스가 공유할수 있다.
  하지만 동시에 엑세스할 수는 없으므로 동시에 액세스 하려는 프로세스 간 액세스를 직렬화 하기위한 Lock 메커니즘으로서 래치(Latch)가 존재한다.
• PGA는 다른 프로세스와 공유하지 않는 독립적인 메모리 공간으로 래치 메커니즘이 불필요하다.
  같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.

---

기본 메커니즘

소트 머지 조인의 두 단계

1. 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다 → sort area에 저장
2. 머지 단계 : 정렬한 양쪽 집합을 서로 머지한다.

➡️ 양쪽 테이블을 각각 소트한 후, 위쪽 테이블 기준으로 아래쪽 테이블과 머지 조인한다.

-- < 원본 쿼리 >
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

--	  ↓  수행과정 풀이  ↓

-- 1) 사원데이터를 조인 컬럼인 사원번호 순으로 정렬 
--    => 결과 집합은 PGA영역의 Sort Area에 저장
--		 (PGA에 담을 수 없을 정도로 크면 Temp테이블 스페이스에 저장)

select 사원번호, 사원명, 입사일자
from   사원
where  입사일자 >= '19960101'
and    부서코드 = 'Z123'
order by 사원번호;

-- 2) 고객 데이터를 조인컬럼인 관리자사원번호 순으로 정렬
--    => 결과 집합은 PGA영역의 Sort Area에 저장
--	     (PGA에 담을 수 없을 정도로 크면 Temp 테이블스페이스에 저장)

select 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
from   고객 c
where  최종주문금액 >= 2000
order by 관리사원번호;

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

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;

Sort Area에 저장한 데이터 자체가 인덱스 역할을 하므로 소트 머지 조인은 조인 컬럼에 인덱스가 없어도 사용 가능하다

---

소트 머지 조인이 빠른 이유

• nl 조인은 조인 과정에서 액세스하는 모든 블록을 랜덤 액세스 방식으로 건건이 DB 버퍼캐시를 경유해서 읽음
  → 읽는 모든 블록에 래치 획득 및 캐시버퍼 체인 스캔과정을 거침 ( 대량 데이터 조인에는 불리 )
• 소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA에 저장한 후 조인
  → PGA는 독립적인 메모리 공간이므로 래치 획득 과정 없음 ( 대량 데이터 조인에 유리 )

---

소트 머지 조인의 주용도

• 조인 조건식이 등치 ( = ) 조건이 아닌 대량 데이터 조인
• 조인 조건식이 아예 없는 조인 ( Cross Join, 카테시안 곱 )