[Hint] JOIN plan explain trace (NESTED LOOP, SORT MERGE, HASH JOIN ) use_nl, use_merge, use_hash

[Hint]조인 방법 변경(USE_NL)

조인 방법 변경(USE_NL) 테이블을 조인 하는 경우 중첩 루프 조인(Nested Loop Join)이 일어나도록 하는 힌트 문장 입니다. 중첩 루프 조인은 중첩 반복이라고도 하는데 하나의 테이블(outer/driving table)에서 추출된 로우를 가지고 일일이 다른 테이블(inner/probed table)을 반복해서 조회하여 찾아지는 레코드를 최종 데이터로 간주하는 방법 입니다. 즉 조인 입력 한 개를 외부 입력 테이블로 사용하고, 한 개는 내부(최하위) 입력 테이블로 사용하고 외부 루프는 외부 입력 테이블을 행 단위로 사용하고 각 외부 행에 대해 실행되는 내부 루프는 내부 입력 테이블에서 일치되는 행을 검색 하는거죠…  이것을 원시 중첩 루프 조인이라고 하는데 검색에서 인덱스를 사용하는 경우에는 인덱스 중첩 루프 조인이라고 합니다. 예를 들어 EMP 테이블과 DEPT 테이블을 조인하는 경우 dept 테이블이 건수가 작다면 우선 이 테이블을 외부 루프로 해서 하나씩 읽으면서 이에 대응하는 emp 테이블의 데이터를 추출 하는 경우라면 중첩 루프 조인에 해당 합니다. 이때 emp 테이블의 경우 건수가 많다고 가정을 하면 대부분 인덱스를 이용하도록 emp 테이블의 외래키인 deptno 컬럼은 대부분 인덱스를 걸게 되죠^^ 중첩 루프 조인은 테이블중 적어도 하나의 조인 컬럼에 대해 인덱스(or Hash Index)가 존재할 때 연관되는 방식으로 이 중첩 루프 조인에서 테이블중 하나의 테이블 또는 중간 결과 셋을 대상으로 FULL SCAN이 일어나게 됩니다. 이 테이블이 드라이빙 테이블이 되는데… 이 테이블의 데이터 건마다 나머지 테이블에서 원하는 데이터를 추출하기 위해 대부분 인덱스를 사용하게 되는 겁니다. 보통 USE_NL 힌트 구문은 ORDERED 힌트 구문과 같이 사용되는데 USE_NL이 취하는 인자는 FROM절에서 두번째 나오는 테이블(비드라이빙 테이블, inner/probed table)을 명시해 주어야 합니다. 안수로 사용되지 않은 첫 번째 테이블은  outer/driving table이 되는 것입니다. [형식] /*+ USE_NL ( table [table]... ) */ [예] 아래는 Oracle 10g에서 테스트 한 결과 입니다. analyze table emp compute statistics analyze table dept compute statistics select /*+ORDERED USE_NLe) */        e.ename,            d.dname from   dept d, emp e where  e.deptno = d.deptno         ------------------------------------------------------------ Operation        Object Name        Rows        Bytes        Cost         --------------------------------------------------------------- SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   4   TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        4          32          1               NESTED LOOPS                14          266          4                                         TABLE ACCESS FULL        SCOTT.DEPT        4          44          3                           INDEX RANGE SCAN        SCOTT.IDX_EMP_DEPTNO        5                   0           FROM절에서 처음 나타나는 테이블이 드라이빙 테이블(DRIVING/OUTER? TABLE)이며 비드라이빙 테이블(PROBE/INNER TABLE)이 USE_NL의 인자로 들어갑니다!! select /*+ORDERED USE_NL(D) */        e.ename,            d.dname from   emp e, dept d where  e.deptno = d.deptno         -------------------------------------------------------------- Operation        Object Name        Rows        Bytes        Cost         -------------------------------------------------------------- SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   3   NESTED LOOPS                14          266          3                                       TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          112          2                 INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1     TABLE ACCESS BY INDEX ROWID        SCOTT.DEPT        1          11          1                 INDEX UNIQUE SCAN        SCOTT.PK_DEPT        1                   0                                                                이번에는 USE_MERGE와 ORDERED가 같이 쓰이는 경우인데 이 경우엔 FROM 절 뒤 테이블의 순서는 실행계획은 다르게 나티날지 모르지만 성능에는 영향을 미치지 않습니다. 왜냐구요? 위 내용을 읽어 보세요!! select /*+ORDERED USE_MERGE(D) */        e.ename,            d.dname from   emp e, dept d where  e.deptno = d.deptno         -------------------------------------------------------------- Operation        Object Name        Rows        Bytes        Cost         ------------------------------------------------------------- SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   6   MERGE JOIN                14          266          6                                                           TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          112          2                 INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1     SORT JOIN                4          44          4                                                             TABLE ACCESS FULL        SCOTT.DEPT        4          44          3                                                       select /*+ ORDERED USE_MERGE(E) */        e.ename,            d.dname 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          266          5                                                           TABLE ACCESS BY INDEX ROWID        SCOTT.DEPT        4          44          2                 INDEX FULL SCAN        SCOTT.PK_DEPT        4                   1               SORT JOIN                14          112          3                                                             TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          112          2                   INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1

http://blog.paran.com/oraclejava/9910752

 

 

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

 

 

NESTED LOOP JOIN

 

선행적 특징을 작는데 먼저 액세스되는 테이블의 처리범위에 의해 처리량이 결정됨
Driving Table에 의해 범위가 결정되며 Driving Table의 범위가 적을수록 수행속도는 빨라진다
고로 Driving Table을 어던 테이블로 결정하느냐가 중요하다

-. /*+ use_nl (테이블) */
-. 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스하게됨, 즉 값을 받아서 처리범위가 정해짐
-. Driving Table의 인덱스 액세스는 첫번 로우만 Random Access이고, 나머지는 Scan, 연결작업은 Random Access임
-. 연결되는 방향에 따라 사용되는 인덱스들이 달라질 수 있음
-. 연결고리 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 있음
-. 연결작업 수행 후 체크되는 조건으로 부분범위처리를 하는 경우에는 조건의 범위가 넓거나 없다면 오히려 빨라짐

-. 전체가 아닌 부분범위 처리를 하는 경우 유리함
-. 조인되는 테이블중 어느 한쪽의 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리함
-. Driving Table의 처리량이 많거나 연결 테이블의 Random Access량이 많을 경우에는 분리함
-. 일반적으로 처리량이 적은 경우로서 Random Access를 많이 하므로, 온라인 어플리에서 유리함
-. Driving Table의 선택이 관건임

 

SORT MERGE JOIN

 

일반적으로 배치작업에서 주로 사용되며, 각 테이블을 Sort한 후 Merge 하는 조인을 말한다

 

-. /*+ use_merge(테이블) */
-. 동시에 각각의 테이블이 자신의 처리범위를 액세스하여 정렬해둠
-. 각 테이블은 어떠한 상수값도 서로 영향을 주지 않으며, 주어진 상수값에 의해서만 각자 범위를 줄이게됨
-. 전체범위처리를하며 부분범위처리를 할수 없음
-. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우에만 Random Access이고, Merge작업은 Scan방식
-. 선택적으로 연결고리가 되는 컬럼은 인덱스를 사용하지 않음
-. 조인의 방향과는 상관없음
-. Equal 조인에서만 가능

-. 처리량이 많은 경우로 Random Access를 하지 않음으로 전체범위처리에 유리
-. 자신의 처리범위를 인덱스를 통해 어떻게 줄이느냐가 관건
-. 상수값을 받아 줄여진 범위가 30%이상이면 Sort Merge가 유리

 

 

HASH JOIN

 

Hash Function을 이용해서 메모리와 CPU를 많이 사용해서 일반적으로 배치작업에서 주로 사용됨

-. /*+ use_hash(테이블) */
-. 적은테이블과 큰테이블의 조인시에 유리
-. Equal 조인에서만 가능
-. Driving Table에 인덱스를 필요로 하지 않고 각 테이블을 한번만 읽음
-. 다른조인방법보다 CPU자원을 많이 소비하며 양쪽 테이블의 scan이 동시에 일어남

 

from) http://www.jakartaproject.com/

[출처] NESTED LOOP, SORT MERGE, HASH JOIN|작성자 은빛간달