oracle - [Hint]조인 방법 변경(USE_HASH)

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

해시 조인(Hash-Join)은 두 테이블 중 하나를 기준으로 비트맵 해시 테이블을 메모리에 올린 후 나머지 테이블을 스캔 하면서 해싱 테이블을 적용하여 메모리에 로딩된 테이블과 비교하여 매칭되는 데이터를 추출하는 방식 입니다.

성능을 위해서는 당연히 사이즈가 작은 테이블이 메모리에 올라가는 것이 좋으며 이때 이 테이블을 드라이빙 테이블(driving/outer table) 이라고 합니다. 특히 이 해시 테이블이 메모리에 생성되면 성능은 좋으며(메모리에 생성되지 않으면 내부적으로 임시 테이블이 만들어 져야 합니다.) 두 테이블의 크기 차이가 클수록 성능은 좋아집니다.

또한 해시 조인은 안티 조인과 병렬처리와 잘 맞으며 범위 검색(Range scan)이 아닌 동등 비교(Equi-Join, where절에서 등호로 비교하는 경우)에 더 적합 합니다.

[형식]

/*+ USE_HASH ( table [table]... ) */

select /*+ ordered use_hash(큰테이블) */ … from 작은테이블, 큰테이블

[예]

아래는 Oracle 10g에서 테스트 했습니다.

아래에서 dept 테이블이 메모리에 로드되어 emp 테이블의 내용과 비교하면서 결과를 추출 합니다.

select    /*+ ORDERED USE_HASH(e) */

           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                   6 

  HASH JOIN                14          406          6                                                       

    TABLE ACCESS FULL        SCOTT.DEPT        4          72          3                    

    TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          154          2           

      INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1  

select /*+ ORDERED USE_HASH(d) */

       e.empno,

           e.ename,

           d.dname,

           d.loc

from   emp e, dept d

where  e.deptno = d.deptno        

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

Operation        Object Name        Rows        Bytes        Cost        

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

SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   6 

  HASH JOIN                14          406          6                                                       

    TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          154          2           

      INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1 

    TABLE ACCESS FULL        SCOTT.DEPT        4          72          3                         

참고로 ALL_ROWS인 경우엔 머지 조인과 해시 조인을 비교한다면 해시 조인의 성능이 좋으며 중첩 조인의 경우 주로 첫번째 로우를 빠르게 추출하기 위한 FIRST_ROWS로 수행되는 조인 입니다.