소트 머지 조인
조인 컬럼에 인덱스가 없을 때 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때 옵티마이저는 NL 조인 대산 소트 머지 조인이나 해시 조인을 사용합니다
SGA VS PGA
오라클 공유 메모리 영역인 SGA 캐시 된 데이터는 여러 프로세스가 공유할 수는 있지만 한 번에 하나의 래치만 동작해서 해시를 하고 결과 셋을 줍니다 즉 여러 프로세스가 하나의 블록에 접근하더라도 접근하는 순서에 따라서 해시 - 래치 결과를 주게 됩니다 오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서 동시에 자신만의 고유 메모리 영역을 갖게 됩니다 이를 PGA라고 부르며 프로세스에 종속적인 공유 데이터를 저장하는 용도로 사용합니다 PGA는 독립적인 메모리 공간이므로 해시 - 래치 프로세스가 불필요하다 그래서 같은 양의 데이터라도 읽는 속도가 SGA보다는 빠르다
소트머진 기본 메커니즘
양쪽 집합을 조인 컬럼 기준으로 정렬
정렬한 양쪽 집합을 서로 머지 한다
앞의 쿼리를 가져와보자
1
2
3
4
5
6
7
SELECT
* FROM Ex18_emp e INNER JOIN Ex18_custom c ON e.emp_cd = c.emp_code
WHERE e.join_date BETWEEN '19950101' AND '19992131'
AND e.emp_dept_cd >= 20
AND c.custom_sal_amt > 3000
소트 조인이 일어난다면
Ex18_emp Ex18_custom 가 emp_cd 기준으로 정렬
emp_cd 기준으로 정렬된 두 개의 테이블 Ex18_emp Ex18_custom 을 emp_cd 기준으로 병합한다
소트머지 힌트
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
EXPLAIN PLAN FOR
SELECT /*+ use_merge(c) index(e) index(c)*/
* FROM Ex18_emp e INNER JOIN Ex18_custom c ON e.emp_cd = c.emp_code
WHERE e.join_date BETWEEN '19950101' AND '19992131'
AND e.emp_dept_cd >= 20
AND c.custom_sal_amt > 3000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------------------------------+
Plan hash value: 3847668752 |
|
---------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 67 | 9648 | 742 (0)| 00:00:01 ||
|* 1 | HASH JOIN | | 67 | 9648 | 742 (0)| 00:00:01 ||
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EX18_EMP | 1 | 51 | 6 (0)| 00:00:01 ||
|* 3 | INDEX RANGE SCAN | EX18_EMP_IDX | 6 | | 5 (0)| 00:00:01 ||
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EX18_CUSTOM | 2427 | 220K| 736 (0)| 00:00:01 ||
| 5 | INDEX FULL SCAN | EX18_CUSTOM_IDX1 | 5000 | | 57 (0)| 00:00:01 ||
---------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("E"."EMP_CD"="C"."EMP_CODE") |
2 - filter(TO_NUMBER("E"."EMP_DEPT_CD")>=20) |
3 - access("E"."JOIN_DATE">='19950101' AND "E"."JOIN_DATE"<='19992131') |
4 - filter("C"."CUSTOM_SAL_AMT">3000) |
|
Hint Report (identified by operation id / Query Block Name / Object Alias): |
Total hints for statement: 1 (U - Unused (1)) |
--------------------------------------------------------------------------- |
|
4 - SEL$58A6D7F6 / "C"@"SEL$1" |
U - use_merge(c) |
소트 머지 실행계획인데 하나씩 뜯어서 살펴보면 참고로 소트 머지 힌트는 use_merge로 힌트 제어를 하게 됩니다
소트 1번
1
2
3
4
5
6
7
8
9
10
SELECT emp_cd ,
emp_dept_cd,
join_date
FROM Ex18_emp
WHERE join_date BETWEEN '19950101' AND '19992131'
AND emp_dept_cd >= 20
ORDER BY emp_cd
먼저 Ex18_emp 테이블을 이와 같이 where 절 조건과 정렬을 하게 됩니다
소트 2번
1
2
3
4
5
6
7
8
9
SELECT custom_code ,
custom_sal_amt,
sal_date,
emp_code
FROM Ex18_custom
WHERE custom_sal_amt > 3000
ORDER BY emp_code
마찬가지로 Ex18_custom 테이블에 where 절 조건과 정렬을 하게 됩니다
그리고 정렬한 결과 집합은 PGA 영영에 할당된 Sort Area에 저장이 되지만 결과 셋이 너무 크다면 Temp 테이블 스페이스에 저장됩니다
그리고 반복문을 돌면서 Ex18_emp의 emp_cd 와 Ex18_custom의 emp_code 가 맞물리는 데이터를 조인하는 게 됩니다
머지 3번
1
2
3
4
5
6
7
8
9
10
11
12
13
14
for(int i = 0; i < PGA_Ex18_emp.size(); i++){
String emp_cd = PGA_Ex18_emp.get(i).getEmpCd();
for(int j = 0; j < PGA_Ex18_custom.size(); j++){
String emp_code = PGA_Ex18_custom.get(j).getEmpCode();
if(emp_code.equals(emp_cd)){
return PGA_Ex18_emp.get(i) + PGA_Ex18_custom.get(j)
}
}
}
대략적으로 이렇게 들어옵니다 PGA 영역에 들어온 애들은 이미 정렬과 필터를 거친 상태이므로 이 상태에서는 이제 머지만 하면 되기 때문에 이를 프로그래밍언어로 옮기면 이렇게 됩니다 실제 조인 오퍼레이션을 행하는 곳은 3번에서 행하게 된다 그리고 이미 데이터가 정렬되어 있는 상태이기 때문에 절대로 full scan 을 하지 않게 됩니다 즉 이때 Range Index Scan 이 발생하며 시작하는 부분과 멈추는 부분을 정확하게 집어낼 수 있게 됩니다 이때 PGA에 저장되어 있는 데이터 자체가 인덱스 역할을 하기 때문에 그렇게 됩니다
소트 머지 조인이 빠른 이유
NL 조인은 정통적인 DBMS 에서 제공하는 가장 보편화된 조인 방식이다 하지만 단점은 대량데이터를 NL 조인을 하게 되면 성능이 매우 느리다는 단점이 있습니다 하지만 위에서 보았듯이 조인 오퍼레이션 동작 방법은 NL 조인방식인데 대량 데이터 조인시 소트 머지 조인이 빠른 이유가 무엇일까? NL 조인은 인덱스를 이용한 조인이다 보니 랜덤 I/O 가 발생하며 이때 래치 메커니즘때문에 읽는데 시간이 걸리기 마련입니다 이는 인덱스의 손익 분기점 한게를 그대로 드러냅니다 이것이 대량 조인에서 NL 조인이 불리한 이유입니다
하지만 소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 저장한 후 저인합니다 이때 PGA 는 독립적인 공간임으로 래치 매커니즘이 필요 없게 되고 그만큼 속도도 빠릅니다 하지만 소트머지도 조인 대상 집합을 읽을 때는 인덱스를 이용한 랜덤 I/O 가 발생할 수 있기 때문에 이를 피할 수는 없습니다
단점
대량 데이터를 정렬을 먼저 하다 보니 데이터가 많으면 많을 수록 이 정렬에 해당되는 비용을 많이 소모하는것이 단점입니다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
EXPLAIN PLAN FOR
SELECT /*+ ordered use_merge(c)*/
* FROM Ex19_emp e INNER JOIN Ex19_custom c ON e.emp_no <= c.emp_code
WHERE e.join_date BETWEEN '19950101' AND '19992131'
AND e.emp_dept_cd >= 20
AND c.custom_sal_amt > 3000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
------------------------------------------------------------------------------------------------------------------+
Plan hash value: 711813126 |
|
------------------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time ||
------------------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 2357 | 432K| | 776 (1)| 00:00:01 ||
| 1 | MERGE JOIN | | 2357 | 432K| | 776 (1)| 00:00:01 ||
| 2 | SORT JOIN | | 2 | 130 | | 2 (50)| 00:00:01 ||
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| EX19_EMP | 2 | 130 | | 1 (0)| 00:00:01 ||
|* 4 | INDEX RANGE SCAN | EX19_EMP_IDX | 2 | | | 1 (0)| 00:00:01 ||
|* 5 | SORT JOIN | | 23568 | 2830K| 6296K| 774 (1)| 00:00:01 ||
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| EX19_CUSTOM | 23568 | 2830K| | 120 (0)| 00:00:01 ||
|* 7 | INDEX RANGE SCAN | EX19_CUSTOM_IDX2 | 23568 | | | 120 (0)| 00:00:01 ||
------------------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
3 - filter(TO_NUMBER("E"."EMP_DEPT_CD")>=20) |
4 - access("E"."JOIN_DATE">='19950101' AND "E"."JOIN_DATE"<='19992131') |
5 - access("E"."EMP_NO"<="C"."EMP_CODE") |
filter("E"."EMP_NO"<="C"."EMP_CODE") |
7 - access("C"."CUSTOM_SAL_AMT">3000) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |