Oracle. 실행계획 - JOIN

[ Oracle. 실행계획 - JOIN ]

 

1. JOIN연산 종류

    1. Nested Loop Join

    2. Sort Merge Join

    3. Hash Join

 

2. Nested Loop Join

    - sql > SELECT /*+ USE_NL(emp_large, depart) */ dname, ename, job, sal

                  FROM emp_large a,  depart b

                WHERE a.deptno = b.deptno;

    - 실행계획

        ; NESTED LOOPS 수행방식

           . 2번 id를 먼저 수행 후 4,3 순서로 수행 됨

           . EMP_LARGE 에서 1건의 데이터를 읽어서 DEPART테이블에 접근하는 것을 반복하는 방식

             LOOP 라는 용어가 들어간 이유이다.

           . DEPART테이블을 하나씩 접근하므로 INDEX UNIQUE SCAN을 하게 된다.

      

 

    - 수행방법

        1. 조건절에 적용된 기준으로 emp_large테이블을 먼저 읽어서 SGA영역에 위치

        2. emp_large의 각 레코드 1개씩 depart 테이블과 join한다.

    - 특징

        1. 중첩된(Nested)Loop방식으로 처리되는 알고리즘

            (C언어나 기타 개발언어에서 종종 보던 중첩 Loop와 동일)

        2. 소량의 데이터 처리용

     - hine

         1. SELECT /*+ USE_NL(테이블1, 테이블2) */..

         2. SELECT /*+ ORDERED USE_NL(테이블1, 테이블2) */..

             . ORDERED란 FROM절에 기술된 테이블 순서대로 Nested Loop Join을 수행하라는 의미

 

3. Sort Merge Join

    - sql > SELECT /*+ USE_MERGE(emp_large, depart) */ dname, ename, job, sal

                  FROM emp_large a,  depart b

                WHERE a.deptno = b.deptno;

    - 실행계획

       ; id 2,4 각각 자료를 조회 후 정렬한 다음 join조건으로 JOIN을 하게 됨

         (depart는 index full scan이므로 정렬을 하지 않았음)

    

   - 특징

       . Sort merge Join을 할 때는 인덱스가 없어도 수행 될 수 있다.

       . Optimizer Mode가 ALL_ROWS인 경우에 자주 발생

       . Online 사용자에게는 부적합한 Join 알고리즘 임

       . Sort 연산에 많은 처리 비용이 발생

       . 대량의 데이터 처리용

 

4. Hash Join

    - sql > SELECT /*+ USE_HASH(emp_large, depart) */ dname, ename, job, sal

                  FROM emp_large a,  depart b

                WHERE a.deptno = b.deptno;

    - 실행계획

       참고) Sort merge Join에서는 full Table Scan을 수행한 후 Sort연산을 수행했었음.

       ; HASH JOIN

          . Sort Merge Join과 유사하게 접근하지만 Sort연산 대신 Hash 연산 사용

     

    - 장점

       . Sort Merge Join성능을 개선하려는 시도로 등장한 방법

    - 수행순서

       1. Sort 연산 후

       2. Merge 연산을 통해

       3. Join하는 방식

   - 특징

       . Sort Merge Join에 대한 성능 향상을 위해 7.3버전에 새로 등장한 알고리즘

       . '='연산자를 사용하는 경우에만 사용이 가능

       . Hash 함수와 Hash 테이블을 사용

          (Hash 함수 : 메모리상에 생성되는 데이터 집합)

       . Hash function : 데이터값 arg값으로 받아서 중복을 최소화한 고유한 주소 데이터를 생성함

       . Sort를 하지 않고도 대량의 데이터를 처리 할 수 있음.

 

9. 종합

    1. 전체 데이터 중 소량의 데이터를 Join으로 가져 올 때는 Sort Merge Join보다는

        Nested Loop Join이 효율적일 수 있다.

    2. OLTP성 트랙잭션 유형에는 Nested Loop Join이 어울린다.

    3. 배치성(DSS)성 트랜잭션 유형에는 Sort Merge Join/Hash Join이 어울린다.

    4. join처리 알고리즘

                        

 

      5. Optimizer 실행계획 3단계

          . Query Transformation -> Query Optimization -> Generate Execution Plan