[SQL초보전문가]조인 방법 변경(USE_MERGE) , SQL교육,오라클힌트 교육, SQL강좌

Hint]조인 방법 변경(USE_MERGE)

 

구로디지털 오엔제이프로그래밍실무교육센터

www.onjprogramming.co.kr

 

 

머지 조인(Merge Join)이 일어나도록 유도하는 힌트 구문으로 이 경우 거의 SORT를 동반하므로 SORT MERGE JOIN이라고 부릅니다. 머지 조인이란 양쪽 테이블에서 대상 로우를 추출 후 조인 컬럼을 기준으로 SORT를 한 후 최종 결과를 만들어 내는 조인 방식 입니다.

 

USE_NL처럼 FROM 절 다음에 위치하는 테이블의 순서는 중요하지 않은데 그 이유는 어차피 독립적으로 정렬된 후 병합이 일어나므로 중요하지 않다고 할 수 있으며 SORT MERGE JOIN에서는 드라이빙 테이블의 의미가 없습니다.

 

[형식]

 

 

[예]

아래 예제는 Oracle 10g에서 돌렸습니다.

 

 

select 

       e.empno,

          e.ename,

          d.dname,

          d.loc

from   dept d, emp e

where  e.deptno = d.deptno

 

---------------------------------------------------------------

Operation            Object Name      Rows     Bytes    Cost     

-------------------------------------------------------------

SELECT STATEMENT Optimizer Mode=ALL_ROWS               14                      5

  MERGE JOIN                  14         406       5                                                       

    TABLE ACCESS BY INDEX ROWID         SCOTT.DEPT      4           72         2            

      INDEX FULL SCAN   SCOTT.PK_DEPT             4                        1            

    SORT JOIN                 14         154       3                                                       

      TABLE ACCESS BY INDEX ROWID      SCOTT.EMP        14         154       2            

        INDEX FULL SCAN             SCOTT.IDX_EMP_DEPTNO            13                      1                                                           

 

select 

       e.empno,

          e.ename,

          d.dname,

          d.loc

from   dept d, emp e

where  e.deptno = d.deptno

 

---------------------------------------------------------------------

Operation            Object Name      Rows     Bytes    Cost     

------------------------------------------------------------------

SELECT STATEMENT Optimizer Mode=ALL_ROWS               14                      5

  MERGE JOIN                  14         406       5                                                       

    TABLE ACCESS BY INDEX ROWID         SCOTT.DEPT      4           72         2            

      INDEX FULL SCAN   SCOTT.PK_DEPT             4                        1                SORT JOIN                14         154               3                                                       

      TABLE ACCESS BY INDEX ROWID      SCOTT.EMP        14         154       2            

        INDEX FULL SCAN             SCOTT.IDX_EMP_DEPTNO            13                      1                                                           

 

[실습]

 

-      실습을 위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.

http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecOrccleTun&wr_id=53&page=0&sca=&sfl=&stx=&sst=&sod=&spt=0&page=0

 

myemp1 : 1000만건

myemp1_old : 100만건

mydept : 5건

 

테스트환경 : oracle 11g

 

 

MYEMP1이 비드라이빙 테이블이지만 머지조인 에서는 별 의미 없다.

 

SQL> select

  2         e.ename,

  3         d.dname

  4  from   mydept1 d, myemp1 e

  5  where  e.deptno = d.deptno   ;

 

20000000 개의 행이 선택되었습니다.

 

경   과: 00:02:22.62

 

 

---------------------------------------------------------------------------------------

| Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |         |    20M|   476M|       | 68516   (2)| 00:13:43 |

|   1 |  MERGE JOIN         |         |    20M|   476M|       | 68516   (2)| 00:13:43 |

|   2 |   SORT JOIN         |         |    10 |   100 |       |     4  (25)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| MYDEPT1 |    10 |   100 |       |     3   (0)| 00:00:01 |

|*  4 |   SORT JOIN         |         |    10M|   143M|   459M| 68463   (1)| 00:13:42 |

|   5 |    TABLE ACCESS FULL| MYEMP1  |    10M|   143M|       | 16941   (1)| 00:03:24 |

 

 

SQL> select

  2         e.ename,

  3         d.dname

  4  from   myemp1 e, mydept1 d

  5  where  e.deptno = d.deptno ;

 

20000000 개의 행이 선택되었습니다.

 

경   과: 00:02:09.58

 

---------------------------------------------------------------------------------------

| Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |         |    20M|   476M|       | 68516   (2)| 00:13:43 |

|   1 |  MERGE JOIN         |         |    20M|   476M|       | 68516   (2)| 00:13:43 |

|   2 |   SORT JOIN         |         |    10M|   143M|   459M| 68463   (1)| 00:13:42 |

|   3 |    TABLE ACCESS FULL| MYEMP1  |    10M|   143M|       | 16941   (1)| 00:03:24 |

|*  4 |   SORT JOIN         |         |    10 |   100 |       |     4  (25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL| MYDEPT1 |    10 |   100 |       |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

  

[출처] 오라클자바커뮤니티 - http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecHINT&wr_id=91