02 소트머지 조인

(1) 기본 메커니즘

• 두 테이블을 각각 정렬한 다음에 두 집합을 머지(Merge)하면서 조인을 수행한다
  • 소트단계:양쪽 집합을 조인 컬럼 기준으로 정렬
  • 머지단계:정렬된 양쪽 집합을 서로 Merge
• 소트머지 조인은 Outer루프와 Inner루프가 Sort Area에 미리 정렬해둔 자료구조를 이용할분 실제 조인오퍼레이션은 NL조인과 동일
• 하지만 Sort Area가 PGA영역에 할당되므로 래치획득과정이 없으므로 SGA를 경유하는 것보다 훨씬 빠름

select /*+ ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
from dept d, emp e
where d.deptno = e.deptno

• 처리과정
  • Outer테이블인 dept를 deptno기준으로 정렬
  • Inner테이블인 emp를 deptno기준으로 정렬
  • Sort Area에 정렬된 dept테이블을 스캔하면서, 정렬된 emp테이블과조인

• (그림2-3)
  • emp테이블이 정렬되어 있으므로 조인에 실패하는 레코드를 만나는 순간 멈출수 있음.
  • 정렬된 emp에서 스팬 시작접을 찾으려고 매번 탐색하지 않아도 됨.

• (그림2-4)
  • M:M관계인 경우는 두가지를 생각해 볼 수 있음
  • 시작점을 찾으려고 매번 이진탐색 수행
  • 변수를 하나 더 선언해서 스캔했던 시작점을 기억 => 이게 더 설득력 있음

(2) 소트머지 조인의 특징

• PGA영역에 저당된 데이터를 이용하므로 빠르므로 소트부하만 감수하면 NL조인보다 유리
• 인덱스유무에 영향을 받지 않음
• 스캔위주의 액세스방식을 이용
  (단, 양쪽 소스 집합에서 정렬 대상 레코드를 찾는 작업은 인덱스를 이용해 Random엑세스 방식으로 처리, 이때 액세스량이 많다면, 소트머지 이점이 사라질수 있음)
• 대부분 해시조인인 보다 느린 성능을 보이나, 아래와 같은 상황에서는 소트머지 조인이 유용
  • First테이블에 소트연산을 대체할 인덱스가 있을때
  • 조인할 First 집합이 이미 정렬되어 있을때
  • 조인 조건식이 등치(=)조건이 아닐때

(3) First테이블에 소트 연산을 대체할 인덱스가 있을 때

SQL> create index dept_idx on dept(loc, deptno);

인덱스가 생성되었습니다.

SQL> create index emp_idx on emp(job, deptno);

인덱스가 생성되었습니다.

select /*+ ordered use_merge(e) */ *
from dept d, emp e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
and e.job = 'SALESMAN'
order by e.deptno


------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    59 |     5  (20)| 00:00:01 |
|   1 |  MERGE JOIN                   |          |     1 |    59 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | DEPT     |     1 |    22 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | DEPT_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                   |          |     3 |   111 |     3  (34)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP      |     3 |   111 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | EMP_IDX  |     3 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

• dept테이블에 별도의 Sort Join오퍼레이션을 수행하지 않음
• 단 소트 머지 조인에서 인덱스를 이용해 소트연산을 대체할 수 있는 대상은 First테이블에 국한됨

소트 머지 조인에서의 부분범위 처리 활용

select /*+ ordered use_merge(e) index(d dept_pk) */
       d.dname, e.empno, e.ename
from dept d, emp e
where e.deptno = d.deptno;

(그림2-5)

• 소트머지 조인도 부분적으로 부분범위처리가 가능

• 작은 dept테이블이 First테이블이 되도록 소트머지조인

select /*+ leading(d0 use_merge(e) full(d) full(e) */ *
from t_emp e, dept d
where d.deptno = e.deptno

• 큰 t_emp테이블이 First테이블이 되도록 소트머지조인

select /*+ ordered use_merge(d) full(d) index(e t_emp_idx) */ *
from t_emp e, dept d
where d.deptno = e.deptno

(4) 조인할 First집합이 이미 정렬되어 있을 때

• 소트머지 조인할때, First쪽 집합이 조인컬럼을 기준으로 이미 group by, order by, distinct연산등을 수행하여 정렬된 상태인경우 생략
• Second테이블은 정렬되어 있을지라도 Sort Join오퍼레이션 수행

select /*+ ordered use_merge(d) */ d.deptno, d.dname, e.avg_sal
from (select deptno, avg(sal) avg_sal from emp group by deptno) e
     , dept d
where e.deptno = d.deptno

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |    14 |   406 |     7  (29)| 00:00:01 |
|   1 |  HASH GROUP BY                |                |    14 |   406 |     7  (29)| 00:00:01 |
|   2 |   MERGE JOIN                  |                |    14 |   406 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP            |    14 |    98 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | EMP_DEPTNO_IDX |    14 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |                |     4 |    88 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | DEPT           |     4 |    88 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

• Outer테이블을 group by하고도 Sort Join 오퍼레이션이 나타난 이유는 hash group by로 처리했기 때문

select /*+ ordered use_merge(d) */ d.deptno, d.dname, e.avg_sal
from (select deptno, avg(sal) avg_sal from emp group by deptno order by deptno) e
     , dept d
where e.deptno = d.deptno

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |   117 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN          |      |     3 |   117 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    SORT GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
|*  5 |   SORT JOIN          |      |     4 |    52 |     4  (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | DEPT |     4 |    52 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

• 인라인 뷰에 order by절을 명시해 sort group by로 유도하여 Sort Join 오퍼레이션 생략

select /*+ ordered use_merge(e) */ d.deptno, d.dname, e.avg_sal
from dept d
     , (select deptno, avg(sal) avg_sal from emp group by deptno order by deptno) e
where e.deptno = d.deptno

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     3 |   117 |     7  (29)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |     3 |   117 |     7  (29)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |     3 |    78 |     5  (40)| 00:00:01 |
|   5 |    VIEW                      |         |     3 |    78 |     4  (25)| 00:00:01 |
|   6 |     SORT GROUP BY            |         |     3 |    21 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

(5) 조인 조건식이 등치(=) 조건이 아닐때

• 해시조인은 조인조건식이 등치조건일 때만 사용할수 있으나, 소트머지는 아니어도 사용할 수있다.

select /*+ ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
from dept d, emp e
where d.deptno <= e.deptno


----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     3 |   117 |     7  (29)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |     3 |   117 |     7  (29)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |     3 |    78 |     5  (40)| 00:00:01 |
|   5 |    VIEW                      |         |     3 |    78 |     4  (25)| 00:00:01 |
|   6 |     SORT GROUP BY            |         |     3 |    21 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

(그림2-6)

• dept테이블 deptno 기준으로 오름차순 정렬하도록 order by 절을 추가하여도 sort orer by 오퍼레이션이 나타나지 않음
• 단, 내림차순 정렬 또는 emp테이블 depno를 기준으로 정렬하도록 order by 절 주가하면 sort order by 오퍼레이션단계가 추가
• 부등호를 바꾸면 두집합을 내림차순으로 정렬함

(그림2-7)

• dept테이블 deptno 기준으로 내림차순 정렬하도록 order by 절을 추가하여도 sort orer by 오퍼레이션이 나타나지 않음
• 단, 오름차순 정렬 또는 emp테이블 depno를 기준으로 정렬하도록 order by 절 주가하면 sort order by 오퍼레이션단계가 추가

select /*+ ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
from dept d, emp e
where d.deptno >= e.deptno
order by d.deptno asc;

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     3 |    78 |     6  (17)| 00:00:01 |
|   1 |  SORT ORDER BY                 |         |     3 |    78 |     6  (17)| 00:00:01 |
|   2 |   MERGE JOIN                   |         |     3 |    78 |     6  (17)| 00:00:01 |
|   3 |    SORT JOIN                   |         |     4 |    52 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  6 |    SORT JOIN                   |         |    14 |   182 |     4  (25)| 00:00:01 |
|   7 |     TABLE ACCESS FULL          | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------