Sort Merge Join

PGA

오라클 서버 프로세스는 SGA 에 공유된 데이터를 읽고 쓰면서, 동시에 자신만의 고유 메모리 영역 PGA 를 갖는다. 

• PGA 는 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다.
• 할당받은 PGA 공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블스페이스를 이용한다.
  * temp tbs 는 물리적 disk 형태로 존재한다.
• PGA 는 다른 프로세스와 공유하지 않는 메모리 공간으로, 래치 메커니즘이 불필요하다. 
  → 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.

Sort Merge Join 기본 메커니즘

이름이 의미하는 것처럼 2단계로 진행한다.

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

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

 

sql 수행 과정 설명

SELECT /*+ ordered use_merge(D) */ E.EMPNO, E.ENAME, E.SAL, D.DNAME
FROM EMP E
	INNER JOIN DEPT D
	ON D.DEPTNO = E.DEPTNO
	AND D.DNAME = 'SALES'
WHERE E.SAL > 1500
AND E.JOB = 'SALESMAN'
;
Execution plan
------------------------------------------------------------------
| Id | Operation            | Name   | Card  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0| SELECT STATEMENT     |        |     1 |    32 |   10   (20)|
|   1|  MERGE JOIN          |        |     1 |    32 |   10   (20)|
|   2|   SORT JOIN          |        |     2 |    50 |    5   (20)|
|*  3|    TABLE ACCESS FULL | EMP    |     2 |    50 |    4    (0)|
|*  4|   SORT JOIN          |        |     1 |    13 |    5   (20)|
|*  5|    TABLE ACCESS FULL | DEPT   |     1 |    13 |    4    (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------------------------------
   3 - filter("E"."JOB"='SALESMAN' AND "E"."SAL">1500)
   4 - access("D"."DEPTNO"="E"."DEPTNO")
------------------------------------------------------------------

* use_merge 힌트로 소트 머지 조인을 유도한다.

① 아래 조건에 해당하는 emp 데이터를 읽어 조인 컬럼인 deptno 기준으로 정렬한다.

정렬된 결과집합은 PGA 영역에 할당된 Sort Area 에 저장한다. 정렬한 결과집합이 PGA에 담을 수 없이 크면 Temp 테이블스페이스에 저장한다.

SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO
FROM EMP E
WHERE E.SAL > 1500
AND E.JOB = 'SALESMAN'
ORDER BY E.DEPTNO

② 아래 조건에 해당하는 DEPT 데이터를 읽어 조인컬럼인 DEPTNO 기준으로 정렬한다. 

정렬된 결과집합은 PGA 영역에 할당된 Sort Area 에 저장한다. 정렬한 결과집합이 PGA에 담을 수 없이 크면 Temp 테이블스페이스에 저장한다.

SELECT D.DNAME, D.DEPTNO
  FROM DEPT D
 WHERE D.DNAME = 'SALES'
 ORDER BY D.DEPTNO

③ PGA(또는 Temp TBS)에 저장한 EMP 데이터를 스캔하면서 PGA(또는 Temp TBS)에 저장한 DEPT 데이터와 조인한다. 

이 과정이 머지단계이다. NL 조인과 동일한 구조이다.(이중 FOR 문) 

주목할 점은 DEPT 테이블도 DEPTNO 로 정렬되어 있기 때문에 EMP 데이터를 기준으로 DEPT 를 조인할 때 FULL Scan 하지 않아도 된다. 득, scan 시작지점과 끝지점을 쉽게 찾을 수 있다. 속도가 빠르다.

 

 

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

또한, 조인 컬럼에 인덱스가 있어도 nl 조인은 대량 데이터 조인시 불리하므로 소트머지조인을 사용할 수 있다.

 

소트 머지 조인이 빠른 이유

nl 조인은 '인덱스를 이용한 조인 방식'이다.

인덱스를 사용은 액세스할 블록을 랜덤 액세스 방식으로 '건건이' db 버퍼캐시를 경유해서 읽는다.

db 버퍼캐시를 경유하기 때문에 읽는 모든 블록에 래치 획득 및 캐시버퍼 체인 스캔 과정을 거친다. 

 

반면, 소트머지조인은 '조인 조건 이외 필터 조건을 만족하는 2개의 조인 대상 집합을 '일괄적으로' 읽어 DB 버퍼캐시에 로드한 후, 이를 PGA 에 저장하고나서 조인한다.

PGA 는 독립 메모리 공간으로 래치 획득 과정이 없다. 

 

소트 머지 조인의 주용도

• 조인 조건식이 등치(=) 조건이 아닐 때 → 해시 조인 사용이 불가한 경우
• 대량 데이터를 조인할때
• 조인 조건식이 아예 없는 CROSS JOIN 을 수행할 때 → 해시 조인 사용이 불가한 경우

소트 머지 조인 특징 요약

• PGA 영역에 저장한 데이터로 조인 수행하여 속도가 빠름(액세스 경합 발생 x)
• 조인 컬럼에 대한 인덱스 유무에 상관없이 사용 가능
•  scan 위주의 액세스 방식을 사용