by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

2. 소트 머지 조인




소트머지조인

  • 옵티마이저는 조인 컬럼에 적절한 인덱스가 없을 경우, 소트머지조인 or 해시조인 고려
  • 용어정리
    Outer 테이블 Inner 테이블
    First 테이블 Second 테이블
  • 소트머지조인 데모 (PL/SQL)
    데모 (PL/SQL)
    -- 준비
    create table sorted_dept (deptno primary key, dname )
           organization index as
    select deptno, dname from scott.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 scott.emp order by deptno;
    
    -- 실행
    begin
      for outer in (select deptno, empno, rpad(ename, 10) ename from sorted_emp)
      loop
        for inner in (select dname from sorted_dept where deptno = outer.deptno)
        loop
          dbms_output.put_line(outer.empno||' : '||outer.ename||' : ' ||inner.dname);
        end loop;
      end loop;
    end;
    /
    
    -- 결과
    7782 : CLARK      : ACCOUNTING
    7839 : KING       : ACCOUNTING
    7934 : MILLER     : ACCOUNTING
    7369 : SMITH      : RESEARCH
    7566 : JONES      : RESEARCH
    7788 : SCOTT      : RESEARCH
    7876 : ADAMS      : RESEARCH
    7902 : FORD       : RESEARCH
    7499 : ALLEN      : SALES
    7521 : WARD       : SALES
    7654 : MARTIN     : SALES
    7698 : BLAKE      : SALES
    7844 : TURNER     : SALES
    7900 : JAMES      : SALES
    
  • 소트머지조인 힌트 (use_merge)
    힌트 (use_merge)
    select /*+ gather_plan_statistics ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
      from scott.dept d, scott.emp e
     where d.deptno = e.deptno;
    
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |      1 |        |     14 |00:00:00.01 |      11 |       |       |          |
    |   1 |  MERGE JOIN                  |         |      1 |     11 |     14 |00:00:00.01 |      11 |       |       |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       4 |       |       |          |
    |   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
    |*  4 |   SORT JOIN                  |         |      4 |     14 |     14 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("D"."DEPTNO"="E"."DEPTNO")
           filter("D"."DEPTNO"="E"."DEPTNO")
    
  • 소트머지조인 동작 원리
    1. dept 를 deptno 기준으로 정렬
    2. emp 를 deptno 기준으로 정렬
    3. Sort Area 에서, 정렬된 dept 를 스캔 하면서 정렬된 emp 와 조인
      * 조인에 실패하는 레코드를 만나면 멈추고 기억해둠
      * 다음번 조인 시도시 위에서 기억해둔 지점부터 시작
  • 소트머지조인의 특징
    1. 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름 없다
    2. 양쪽 집합 정렬 후에는, NL조인과 같지만, PGA를 사용하므로 더 빠르다
    3. 인덱스의 유무에 영향을 거의 받지 않는다.
    4. 양쪽 집합을 개별적으로 읽은 후 조인 한다.
      * 조인 하기전, 조인 대상을 줄일 수 있다면 아주 유리하다.
    5. 스캔 위주의 액세스다.
      * 조인 하기전, 정렬 대상 레코드를 찾을때 인덱스를 사용해 Random 액세스가 발생하는 경우, 소트머지조인의 이점이 사라짐
    6. 대부분 해시조인 보다 느리다.
      * 예외 (아래에서 상세 설명)
        * First 테이블에 소트 연산을 대체할 인덱스가 있을 때
        * First 집합이 이미 정렬돼 있을 때
        * 조인 조건식이 등치(=) 조건이 아닐 때
    
  • 해시조인 보다 빠를때 - First 테이블에 소트 연산을 대체할 인덱스가 있을 때
    인덱스 없을때#1
     select /*+ gather_plan_statistics ordered use_merge(d e) */ d.deptno, d.dname, e.empno, e.ename
       from scott.dept d, scott.emp e
      where d.deptno = e.deptno
        and d.loc = 'CHICAGO'
        and e.job = 'SALESMAN'
      order by e.deptno;
    
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |      1 |        |      4 |00:00:00.01 |      11 |       |       |          |
    |   1 |  MERGE JOIN                  |         |      1 |      1 |      4 |00:00:00.01 |      11 |       |       |          |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
    |   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
    |*  4 |   SORT JOIN                  |         |      1 |      3 |      4 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |*  5 |    TABLE ACCESS FULL         | EMP     |      1 |      3 |      4 |00:00:00.01 |       7 |       |       |          |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("D"."LOC"='CHICAGO')
       4 - access("D"."DEPTNO"="E"."DEPTNO")
           filter("D"."DEPTNO"="E"."DEPTNO")
       5 - filter("E"."JOB"='SALESMAN')
    
    인덱스 없을때#2
     select /*+ gather_plan_statistics ordered use_merge(d e) full(d) */ d.deptno, d.dname, e.empno, e.ename
       from scott.dept d, scott.emp e
      where d.deptno = e.deptno
        and d.loc = 'CHICAGO'
        and e.job = 'SALESMAN'
      order by e.deptno; 
    
    -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |      1 |        |      4 |00:00:00.01 |      14 |       |       |          |
    |   1 |  MERGE JOIN         |      |      1 |      1 |      4 |00:00:00.01 |      14 |       |       |          |
    |   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |*  3 |    TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
    |*  4 |   SORT JOIN         |      |      1 |      3 |      4 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |*  5 |    TABLE ACCESS FULL| EMP  |      1 |      3 |      4 |00:00:00.01 |       7 |       |       |          |
    -----------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("D"."LOC"='CHICAGO')
       4 - access("D"."DEPTNO"="E"."DEPTNO")
           filter("D"."DEPTNO"="E"."DEPTNO")
       5 - filter("E"."JOB"='SALESMAN')
    
    인덱스 있을때
     create index emp_idx on scott.emp (job, deptno);
     create index dept_idx on scott.dept (loc, deptno);
    
     select /*+ gather_plan_statistics ordered use_merge(d e) */ d.deptno, d.dname, e.empno, e.ename
       from scott.dept d, scott.emp e
      where d.deptno = e.deptno
        and d.loc = 'CHICAGO'
        and e.job = 'SALESMAN'
      order by e.deptno;
    
    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |          |      1 |        |      4 |00:00:00.01 |       5 |       |       |          |
    |   1 |  MERGE JOIN                   |          |      1 |      1 |      4 |00:00:00.01 |       5 |       |       |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID | DEPT     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
    |*  3 |    INDEX RANGE SCAN           | DEPT_IDX |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
    |*  4 |   SORT JOIN                   |          |      1 |      3 |      4 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
    |   5 |    TABLE ACCESS BY INDEX ROWID| EMP      |      1 |      3 |      4 |00:00:00.01 |       2 |       |       |          |
    |*  6 |     INDEX RANGE SCAN          | EMP_IDX  |      1 |      3 |      4 |00:00:00.01 |       1 |       |       |          |
    -------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("D"."LOC"='CHICAGO')
       4 - access("D"."DEPTNO"="E"."DEPTNO")
           filter("D"."DEPTNO"="E"."DEPTNO")
       6 - access("E"."JOB"='SALESMAN')
    
    -- First 테이블과 Second 테이블에 각각 적절한 인덱스가 있으나, First 테이블만 소트 연산이 대체됨
    -- Second 테이블은 SORT JOIN 이 발생 하지만 이미 정렬 되어 있으므로 부하 크지 않음
    -- 항상 First 테이블을 먼저 읽지 않는다
    --   1. scott.dept 는 정렬된 인덱스(dept_idx)가 있으므로 패스
    --   2. scott.emp 를 읽어 정렬한 결과를 Sort Area 에 저장 (Second 테이블인 scott.emp 먼저 읽었음)
    --   3. 조인 연산을 진행할 때, dept_idx 를 읽기 시작
    
  • 해시조인 보다 빠를때 - First 테이블에 소트 연산을 대체할 인덱스가 있을 때 - 부분범위 처리
    소트머지조인 부분범위처리
     select /*+ gather_plan_statistics ordered use_merge(e) index(d dept_pk) */
            d.dname, e.empno, e.ename
       from scott.dept d, scott.emp e
      where e.deptno = d.deptno;
    
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |      1 |        |     14 |00:00:00.01 |      11 |       |       |          |
    |   1 |  MERGE JOIN                  |         |      1 |     11 |     14 |00:00:00.01 |      11 |       |       |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       4 |       |       |          |
    |   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
    |*  4 |   SORT JOIN                  |         |      4 |     14 |     14 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("E"."DEPTNO"="D"."DEPTNO")
           filter("E"."DEPTNO"="D"."DEPTNO")
    
  • 해시조인 보다 빠를때 - First 테이블에 소트 연산을 대체할 인덱스가 있을 때 - 부분범위 처리 데모
    전체범위처리 부분범위처리
    전체범위처리 SQL
    create table t_emp
    as
    select * from scott.emp, (select rownum no from dual connect by level <= 100000);
    
    create index t_emp_idx on t_emp(deptno);
    
    select *
      from (
    select /*+ gather_plan_statistics leading(d) use_merge(e) full(d) full(e) */
           d.dname, e.empno, e.ename
      from scott.dept d, t_emp e
     where e.deptno = d.deptno
           )
     where rownum < 10;
    
    부분범위처리 SQL
    create table t_emp
    as
    select * from scott.emp, (select rownum no from dual connect by level <= 100000);
    
    create index t_emp_idx on t_emp(deptno);
    
    select *
      from (
    select /*+ gather_plan_statistics leading(e) use_merge(d) full(d) index(e t_emp_idx) */
           d.dname, e.empno, e.ename
      from scott.dept d, t_emp e
     where e.deptno = d.deptno
           )
     where rownum < 10;
    
    전체범위처리 실행계획
    ----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation            | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |       |      1 |        |      9 |00:00:01.02 |    9211 |   8996 |       |       |          |
    |*  1 |  COUNT STOPKEY       |       |      1 |        |      9 |00:00:01.02 |    9211 |   8996 |       |       |          |
    |   2 |   MERGE JOIN         |       |      1 |    321K|      9 |00:00:01.02 |    9211 |   8996 |       |       |          |
    |   3 |    SORT JOIN         |       |      1 |      4 |      1 |00:00:00.01 |       7 |      0 |  2048 |  2048 | 2048  (0)|
    |   4 |     TABLE ACCESS FULL| DEPT  |      1 |      4 |      4 |00:00:00.01 |       7 |      0 |       |       |          |
    |*  5 |    SORT JOIN         |       |      1 |   1286K|      9 |00:00:01.02 |    9204 |   8996 |    50M|  2493K|   44M (0)|
    |   6 |     TABLE ACCESS FULL| T_EMP |      1 |   1286K|   1400K|00:00:00.40 |    9204 |   8996 |       |       |          |
    ----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<10)
       5 - access("E"."DEPTNO"="D"."DEPTNO")
           filter("E"."DEPTNO"="D"."DEPTNO")
    
    부분범위처리 실행계획
    --------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |           |      1 |        |      9 |00:00:00.01 |      13 |       |       |          |
    |*  1 |  COUNT STOPKEY                |           |      1 |        |      9 |00:00:00.01 |      13 |       |       |          |
    |   2 |   MERGE JOIN                  |           |      1 |   1286K|      9 |00:00:00.01 |      13 |       |       |          |
    |   3 |    TABLE ACCESS BY INDEX ROWID| T_EMP     |      1 |   1286K|      9 |00:00:00.01 |       6 |       |       |          |
    |   4 |     INDEX FULL SCAN           | T_EMP_IDX |      1 |   1286K|      9 |00:00:00.01 |       4 |       |       |          |
    |*  5 |    SORT JOIN                  |           |      9 |      4 |      9 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   6 |     TABLE ACCESS FULL         | DEPT      |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
    --------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<10)
       5 - access("E"."DEPTNO"="D"."DEPTNO")
           filter("E"."DEPTNO"="D"."DEPTNO")
    
    전체범위처리 Trace
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.02       0.02          0        144          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.70       0.70          0       9211          0           9
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.72       0.73          0       9355          0           9
    
    Misses in library cache during parse: 1
    Optimizer mode: FIRST_ROWS
    Parsing user id: 99  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          9  COUNT STOPKEY (cr=9211 pr=0 pw=0 time=0 us)
          9   MERGE JOIN  (cr=9211 pr=0 pw=0 time=0 us cost=14546 size=19654788 card=333132)
          1    SORT JOIN (cr=7 pr=0 pw=0 time=0 us cost=4 size=52 card=4)
          4     TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=3 us cost=3 size=52 card=4)
          9    SORT JOIN (cr=9204 pr=0 pw=0 time=0 us cost=14542 size=43973457 card=1332529)
    1400000     TABLE ACCESS FULL T_EMP (cr=9204 pr=0 pw=0 time=404205 us cost=2575 size=43973457 card=1332529)
    
    부분범위처리 Trace
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.01       0.02          0        144          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0         13          0           9
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.01       0.02          0        157          0           9
    
    Misses in library cache during parse: 1
    Optimizer mode: FIRST_ROWS
    Parsing user id: 99  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          9  COUNT STOPKEY (cr=13 pr=0 pw=0 time=0 us)
          9   MERGE JOIN  (cr=13 pr=0 pw=0 time=0 us cost=5 size=105269791 card=1332529)
          9    TABLE ACCESS BY INDEX ROWID T_EMP (cr=6 pr=0 pw=0 time=64 us cost=1 size=43973457 card=1332529)
          9     INDEX FULL SCAN T_EMP_IDX (cr=4 pr=0 pw=0 time=48 us cost=1 size=0 card=1332529)(object id 117685)
          9    SORT JOIN (cr=7 pr=0 pw=0 time=0 us cost=4 size=52 card=4)
          4     TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=0 us cost=3 size=52 card=4)
    
  • 해시조인 보다 빠를? - First 집합이 이미 정렬돼 있을 때
    First 집합 정렬안됨(HASH GROUP BY)
    select /*+ gather_plan_statistics ordered use_merge(d) */ d.deptno, d.dname, e.avg_sal
      from (select deptno, avg(sal) avg_sal from scott.emp group by deptno) e,
           scott.dept d
     where e.deptno = d.deptno;
    
    -------------------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |      1 |        |      3 |00:00:00.01 |      14 |       |       |          |
    |   1 |  MERGE JOIN           |      |      1 |      3 |      3 |00:00:00.01 |      14 |       |       |          |
    |   2 |   SORT JOIN           |      |      1 |      3 |      3 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   3 |    VIEW               |      |      1 |      3 |      3 |00:00:00.01 |       7 |       |       |          |
    |   4 |     HASH GROUP BY     |      |      1 |      3 |      3 |00:00:00.01 |       7 |   899K|   899K| 1260K (0)|
    |   5 |      TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    |*  6 |   SORT JOIN           |      |      3 |      4 |      3 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   7 |    TABLE ACCESS FULL  | DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
    -------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       6 - access("E"."DEPTNO"="D"."DEPTNO")
           filter("E"."DEPTNO"="D"."DEPTNO")
    
    First 집합 정렬됨(SORT GROUP BY)
    select /*+ gather_plan_statistics ordered use_merge(d) */ d.deptno, d.dname, e.avg_sal
      from (select deptno, avg(sal) avg_sal from scott.emp group by deptno order by deptno) e,
           scott.dept d
     where e.deptno = d.deptno; 
    
    ------------------------------------------------------------------------------------------------------------------
    | Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |      1 |        |      3 |00:00:00.01 |      14 |       |       |          |
    |   1 |  MERGE JOIN          |      |      1 |      3 |      3 |00:00:00.01 |      14 |       |       |          |
    |   2 |   VIEW               |      |      1 |      3 |      3 |00:00:00.01 |       7 |       |       |          |
    |   3 |    SORT GROUP BY     |      |      1 |      3 |      3 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   4 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    |*  5 |   SORT JOIN          |      |      3 |      4 |      3 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   6 |    TABLE ACCESS FULL | DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
    ------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access("E"."DEPTNO"="D"."DEPTNO")
           filter("E"."DEPTNO"="D"."DEPTNO")
    
    Second 집합 정렬됨(SORT GROUP BY)
    select /*+ gather_plan_statistics ordered use_merge(e) */ d.deptno, d.dname, e.avg_sal
      from scott.dept d,
           (select deptno, avg(sal) avg_sal from scott.emp group by deptno order by deptno) e
     where e.deptno = d.deptno;  
    
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |      1 |        |      3 |00:00:00.01 |      11 |       |       |          |
    |   1 |  MERGE JOIN                  |         |      1 |      3 |      3 |00:00:00.01 |      11 |       |       |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       4 |       |       |          |
    |   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
    |*  4 |   SORT JOIN                  |         |      4 |      3 |      3 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   5 |    VIEW                      |         |      1 |      3 |      3 |00:00:00.01 |       7 |       |       |          |
    |   6 |     SORT GROUP BY            |         |      1 |      3 |      3 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   7 |      TABLE ACCESS FULL       | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("E"."DEPTNO"="D"."DEPTNO")
           filter("E"."DEPTNO"="D"."DEPTNO")
    
  • 해시조인 보다 빠를때 - 조인 조건식이 등치(=) 조건이 아닐 때
    오름차순 내림차순
    ORDER 없음
    select /*+ gather_plan_statistics ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
      from scott.dept d,
           scott.emp e
     where d.deptno <= e.deptno;
    
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |      1 |        |     31 |00:00:00.01 |      15 |       |       |          |
    |   1 |  MERGE JOIN                  |         |      1 |     10 |     31 |00:00:00.01 |      15 |       |       |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       8 |       |       |          |
    |   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       4 |       |       |          |
    |*  4 |   SORT JOIN                  |         |      4 |     14 |     31 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("D"."DEPTNO"<="E"."DEPTNO")
           filter("D"."DEPTNO"<="E"."DEPTNO")
    
    ORDER 없음
    select /*+ gather_plan_statistics ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
      from scott.dept d,
           scott.emp e
     where d.deptno >= e.deptno;
    
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |         |      1 |        |     39 |00:00:00.01 |       9 |       |       |          |
    |   1 |  MERGE JOIN                   |         |      1 |     11 |     39 |00:00:00.01 |       9 |       |       |          |
    |   2 |   SORT JOIN                   |         |      1 |      4 |      4 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
    |   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
    |   4 |     INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       1 |       |       |          |
    |*  5 |   SORT JOIN                   |         |      4 |     14 |     39 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   6 |    TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access(INTERNAL_FUNCTION("D"."DEPTNO")>=INTERNAL_FUNCTION("E"."DEPTNO"))
           filter(INTERNAL_FUNCTION("D"."DEPTNO")>=INTERNAL_FUNCTION("E"."DEPTNO"))
    
    ORDER ASC
    select /*+ gather_plan_statistics ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
      from scott.dept d,
           scott.emp e
     where d.deptno <= e.deptno
     order by d.deptno asc;
    
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |      1 |        |     31 |00:00:00.01 |      15 |       |       |          |
    |   1 |  MERGE JOIN                  |         |      1 |     14 |     31 |00:00:00.01 |      15 |       |       |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       8 |       |       |          |
    |   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      2 |      4 |00:00:00.01 |       4 |       |       |          |
    |*  4 |   SORT JOIN                  |         |      4 |     14 |     31 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("D"."DEPTNO"<="E"."DEPTNO")
           filter("D"."DEPTNO"<="E"."DEPTNO")
    
    
    ORDER ASC
    select /*+ gather_plan_statistics ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
      from scott.dept d,
           scott.emp e
     where d.deptno >= e.deptno
     order by d.deptno asc;
    
    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |         |      1 |        |     39 |00:00:00.01 |       9 |       |       |          |
    |   1 |  SORT ORDER BY                 |         |      1 |     11 |     39 |00:00:00.01 |       9 |  2048 |  2048 | 2048  (0)|
    |   2 |   MERGE JOIN                   |         |      1 |     11 |     39 |00:00:00.01 |       9 |       |       |          |
    |   3 |    SORT JOIN                   |         |      1 |      1 |      4 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
    |   4 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
    |   5 |      INDEX FULL SCAN           | PK_DEPT |      1 |      1 |      4 |00:00:00.01 |       1 |       |       |          |
    |*  6 |    SORT JOIN                   |         |      4 |     14 |     39 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   7 |     TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    -------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       6 - access(INTERNAL_FUNCTION("D"."DEPTNO")>=INTERNAL_FUNCTION("E"."DEPTNO"))
           filter(INTERNAL_FUNCTION("D"."DEPTNO")>=INTERNAL_FUNCTION("E"."DEPTNO"))
    
    ORDER DESC
    select /*+ gather_plan_statistics ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
      from scott.dept d,
           scott.emp e
     where d.deptno <= e.deptno
     order by d.deptno desc;
    
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |         |      1 |        |     31 |00:00:00.01 |       9 |       |       |          |
    |   1 |  SORT ORDER BY                |         |      1 |     28 |     31 |00:00:00.01 |       9 |  2048 |  2048 | 2048  (0)|
    |   2 |   MERGE JOIN                  |         |      1 |     28 |     31 |00:00:00.01 |       9 |       |       |          |
    |   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
    |   4 |     INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       1 |       |       |          |
    |*  5 |    SORT JOIN                  |         |      4 |     14 |     31 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   6 |     TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access("D"."DEPTNO"<="E"."DEPTNO")
           filter("D"."DEPTNO"<="E"."DEPTNO")
    
    ORDER DESC
    select /*+ gather_plan_statistics ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
      from scott.dept d,
           scott.emp e
     where d.deptno >= e.deptno  
     order by d.deptno desc;
    
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |         |      1 |        |     39 |00:00:00.01 |       9 |       |       |          |
    |   1 |  MERGE JOIN                   |         |      1 |     42 |     39 |00:00:00.01 |       9 |       |       |          |
    |   2 |   SORT JOIN                   |         |      1 |      4 |      4 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
    |   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
    |   4 |     INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       1 |       |       |          |
    |*  5 |   SORT JOIN                   |         |      4 |     14 |     39 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
    |   6 |    TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access(INTERNAL_FUNCTION("D"."DEPTNO")>=INTERNAL_FUNCTION("E"."DEPTNO"))
           filter(INTERNAL_FUNCTION("D"."DEPTNO")>=INTERNAL_FUNCTION("E"."DEPTNO"))
    
  • 최초작성자 : 오화균
  • 최초작성일 : 2010년 08월 27일
  • 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.