02. 소트 머지 조인 (Sort Merge Join)

(1) 기본 메커니즘

  create table sorted_dept ( deptno primary key, dname  )

  organization index

  as 

  select deptno, dname from dept order by deptno ;

  create table sorted_emp( empno , ename , deptno

  , constraint sorted_emp_pk primary key(deptno, empno) )

  organization index

  as 

  select empno, ename, deptno from emp order by deptno ;

 

  begin

    for outer in (select deptno, empno, rpad(ename, 10) ename from sorted_emp)

    loop    -- outer 루프

      for inner in (select dname from sorted_dept where deptno = outer.deptno)

      loop  -- inner 루프

        dbms_output.put_line(outer.empno||' : '||outer.ename||' : '||inner.dname);

      end loop;

    end loop;

  end;

=================================================================

  다른점 |  같은점

=================================================================

  a. 자료구조 방법 |  a. 조인 오퍼레이션 수행 과정

  b. PGA 영역 활용 (Sort Area) |  b. Outer 조인 시 순서 고정

=================================================================

  ☞  a. Outer, Inner 루프가 Sort Area에 미리 정렬하는 자료구조 이용

  ☞  b. Sort Area는 PGA 영역에 할당되어 SGA를 경유하여 인덱스와 테이블 액세스할 때보다 훨씬 빠름

          ∵ PGA는 독립적 메모리 공간이기 때문에 데이터 리딩 시, 래치 획득 과정 생략

  Outer  ≒  1st 테이블

  Inner  ≒  2nd 테이블

  select /* ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename

  from   dept d, emp e

  where  d.deptno = e.deptno

1. Outer(=1st) 테이블 dept를 deptno 기준 정렬

2. Inner(=2nd) 테이블 emp 를 deptno 기준 정렬

3. Sort Area에 정렬된 dept 테이블을 스캔하면서, 정렬된 emp 테이블과 조인

정렬된 Inner(=2nd) 테이블에서 스캔 시작점을 찾으려고 매번 탐색하지 않아도 됨

∵ 전단계 스캔 후 멈춘 지점을 기억했다 재탐색

A. 시작점을 찾기 위해 매번 이진 탐색 수행

B. 시작점을 기억하는 변수 선언

   ☞ 소트 머지 조인 시 Outer 테이블까지 정렬한다는 사실을 통해 B 방법일 것이라 저자 추측

      A 방법일 경우, Outer 테이블까지 정렬할 이유가 없다고 판단

(2) 특징

  select /* ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename

  from   dept d, emp e

  where  d.deptno = e.deptno

  and    d.loc = 'CHICAGO'

  and    e.job = 'SALESMAN'

  

ㄱ. 1st 테이블에 소트 연산을 대체할 인덱스 있을 시

ㄴ. 조인할 1st 집합이 이미 정렬되어 있을 시

ㄷ. 조인 조건식이 등치(=)조건이 아닐 시 

☞ 1st 테이블 조인 컬럼에 인덱스가 있을 경우 가능

  create index dept_idx on dept(loc, deptno);

  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;

a. d.loc = 'CHICAGO' 

b. dept_idx(loc, deptno) 인덱스 사용 

   ☞ 결과 집합은 deptno 순으로 정렬 ∴ 소트 머지 조인에서 Sort 연산 생략

a. e.job = 'SALESMAN'

b. emp_idx (job, deptno) 인덱스 사용 

   ☞ 결과 집합은 deptno 순으로 정렬 ∴ 소트 머지 조인에서 Sort 연산 발생 (Sort Join)

※ 소트 머지 조인에서 인덱스를 이용한 소트 연산 대체 대상은 1st 테이블에만 국한

   (단, 위와 같은 경우 2nd 테이블의 Sort 연산에 의한 부하 ↓)

   

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;

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;

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;

--

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