1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2010년 하반기 대용량데이터베이스 스터디
  4. 2. 소트 머지 조인

2. 소트 머지 조인 (by openlsw) [2010.08.23]

소트머지조인

  • 옵티마이저는 조인 컬럼에 적절한 인덱스가 없을 경우, 소트머지조인 or 해시조인 고려
  • 용어정리
Outer 테이블Inner 테이블
First 테이블Second 테이블
  • 소트머지조인 데모 (PL/SQL)
{code:title=데모 (PL/SQL)borderStyle=solid}
-- 준비
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)
|{code:title=힌트 (use_merge)|borderStyle=solid}
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")

|

  • 소트머지조인 동작 원리
{code}1. dept 를 deptno 기준으로 정렬
2. emp 를 deptno 기준으로 정렬
3. Sort Area 에서, 정렬된 dept 를 스캔 하면서 정렬된 emp 와 조인
* 조인에 실패하는 레코드를 만나면 멈추고 기억해둠
* 다음번 조인 시도시 위에서 기억해둔 지점부터 시작{code}
!fig1.PNGalign=left, vspace=4!
  • 소트머지조인의 특징
{code}
1. 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름 없다
2. 양쪽 집합 정렬 후에는, NL조인과 같지만, PGA를 사용하므로 더 빠르다
3. 인덱스의 유무에 영향을 거의 받지 않는다.
4. 양쪽 집합을 개별적으로 읽은 후 조인 한다.
* 조인 하기전, 조인 대상을 줄일 수 있다면 아주 유리하다.
5. 스캔 위주의 액세스다.
* 조인 하기전, 정렬 대상 레코드를 찾을때 인덱스를 사용해 Random 액세스가 발생하는 경우, 소트머지조인의 이점이 사라짐
6. 대부분 해시조인 보다 느리다.
* 예외 (아래에서 상세 설명)
* First 테이블에 소트 연산을 대체할 인덱스가 있을 때
* First 집합이 이미 정렬돼 있을 때
* 조인 조건식이 등치(=) 조건이 아닐 때
{code}
  • 해시조인 보다 빠를때 - First 테이블에 소트 연산을 대체할 인덱스가 있을 때
{code:title=인덱스 없을때#1borderStyle=solid}
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;

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

-

0SELECT STATEMENT1400:00:00.0111
1MERGE JOIN11400:00:00.0111
  • 2
TABLE ACCESS BY INDEX ROWIDDEPT11100:00:00.014
3INDEX FULL SCANPK_DEPT14400:00:00.012
  • 4
SORT JOIN13400:00:00.017204820482048 (0)
  • 5
TABLE ACCESS FULLEMP13400:00:00.017

-

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')

{code:title=인덱스 없을때#2|borderStyle=solid}
 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 테이블에 소트 연산을 대체할 인덱스가 있을 때 - 부분범위 처리
{code:title=소트머지조인 부분범위처리borderStyle=solid}
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;

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

-

0SELECT STATEMENT11400:00:00.0111
1MERGE JOIN1111400:00:00.0111
2TABLE ACCESS BY INDEX ROWIDDEPT14400:00:00.014
3INDEX FULL SCANPK_DEPT14400:00:00.012
  • 4
SORT JOIN4141400:00:00.017204820482048 (0)
5TABLE ACCESS FULLEMP1141400:00:00.017

-

Predicate Information (identified by operation id):

---

4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")

|
|!fig2.PNG|align=center, vspace=4!|
* 해시조인 보다 빠를때 - First 테이블에 소트 연산을 대체할 인덱스가 있을 때 - 부분범위 처리 데모
||전체범위처리||부분범위처리||
|{code:title=전체범위처리 SQL|borderStyle=solid}
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;

|

{code:title=전체범위처리 실행계획borderStyle=solid}
IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsOMem1MemUsed-Mem

0SELECT STATEMENT1900:00:01.0292118996
  • 1
COUNT STOPKEY1900:00:01.0292118996
2MERGE JOIN1321K900:00:01.0292118996
3SORT JOIN14100:00:00.0170204820482048 (0)
4TABLE ACCESS FULLDEPT14400:00:00.0170
  • 5
SORT JOIN11286K900:00:01.029204899650M2493K44M (0)
6TABLE ACCESS FULLT_EMP11286K1400K00:00:00.4092048996

Predicate Information (identified by operation id):

---

1 - filter(ROWNUM<10)
5 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")

|{code:title=부분범위처리 실행계획|borderStyle=solid}
--------------------------------------------------------------------------------------------------------------------------------
| 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")

|

{code:title=전체범위처리 TraceborderStyle=solid}
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)

|{code:title=부분범위처리 Trace|borderStyle=solid}
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 집합이 이미 정렬돼 있을 때
{code:title=First 집합 정렬안됨(HASH GROUP BY)borderStyle=solid}
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;

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

---

0SELECT STATEMENT1300:00:00.0114
1MERGE JOIN13300:00:00.0114
2SORT JOIN13300:00:00.017204820482048 (0)
3VIEW13300:00:00.017
4HASH GROUP BY13300:00:00.017899K899K1260K (0)
5TABLE ACCESS FULLEMP1141400:00:00.017
  • 6
SORT JOIN34300:00:00.017204820482048 (0)
7TABLE ACCESS FULLDEPT14400:00:00.017

---

Predicate Information (identified by operation id):

---

6 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")

|
|{code:title=First 집합 정렬됨(SORT GROUP BY)|borderStyle=solid}
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")

|

{code:title=Second 집합 정렬됨(SORT GROUP BY)borderStyle=solid}
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;

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

-

0SELECT STATEMENT1300:00:00.0111
1MERGE JOIN13300:00:00.0111
2TABLE ACCESS BY INDEX ROWIDDEPT14400:00:00.014
3INDEX FULL SCANPK_DEPT14400:00:00.012
  • 4
SORT JOIN43300:00:00.017204820482048 (0)
5VIEW13300:00:00.017
6SORT GROUP BY13300:00:00.017204820482048 (0)
7TABLE ACCESS FULLEMP1141400:00:00.017

-

Predicate Information (identified by operation id):

---

4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")

|
* 해시조인 보다 빠를때 - 조인 조건식이 등치(=) 조건이 아닐 때
||오름차순||내림차순||
|!fig3.PNG|align=center, vspace=4!|!fig4.PNG|align=center, vspace=4!|
|{code:title=ORDER 없음|borderStyle=solid}
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"))

|

{code:title=ORDER ASCborderStyle=solid}
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;

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

-

0SELECT STATEMENT13100:00:00.0115
1MERGE JOIN1143100:00:00.0115
2TABLE ACCESS BY INDEX ROWIDDEPT14400:00:00.018
3INDEX FULL SCANPK_DEPT12400:00:00.014
  • 4
SORT JOIN4143100:00:00.017204820482048 (0)
5TABLE ACCESS FULLEMP1141400:00:00.017

-

Predicate Information (identified by operation id):

---

4 - access("D"."DEPTNO"<="E"."DEPTNO")
filter("D"."DEPTNO"<="E"."DEPTNO")

|{code:title=ORDER ASC|borderStyle=solid}
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"))

|

{code:title=ORDER DESCborderStyle=solid}
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;

--

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

--

0SELECT STATEMENT13100:00:00.019
1SORT ORDER BY1283100:00:00.019204820482048 (0)
2MERGE JOIN1283100:00:00.019
3TABLE ACCESS BY INDEX ROWIDDEPT14400:00:00.012
4INDEX FULL SCANPK_DEPT14400:00:00.011
  • 5
SORT JOIN4143100:00:00.017204820482048 (0)
6TABLE ACCESS FULLEMP1141400:00:00.017

--

Predicate Information (identified by operation id):

---

5 - access("D"."DEPTNO"<="E"."DEPTNO")
filter("D"."DEPTNO"<="E"."DEPTNO")

|{code:title=ORDER DESC|borderStyle=solid}
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'를 참고하였습니다.*
  1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2010년 하반기 대용량데이터베이스 스터디
  4. 2. 소트 머지 조인