[oracle] Hash Join 테이블 조인순서 바꾸기

SQL PLAN을 튜닝하다보면, 조인순서를 조정할 필요가 있다.
일반적으로는 주어진 조인조건에 따라 오라클 옵티마이저에 의해
조인순서가 정해진다.
또는 FROM절의 테이블순서를 조정한후 ORDERED 힌트를 쓰거나
LEADING 힌트를 써서 최초로 드라이빙되는 테이블만을 지정하는
방법을 쓰거나 INDEX 힌트로 간접적으로 조인순서를 조정키도 한다.

ORDERED 힌트는 전체 테이블 조인순서가 고정되므로 CBO환경에서는
옵티마이저의 유연성이 떨어질 수 있고, 적용력이 강력한 힌트다.
가급적 다른 힌트를 이용해서 SQL을 튜닝하는 것이 권장된다.
어쨋든 ORDERED 힌트를 이용하여 FROM 절 테이블 순서대로 조인되도록
조정한 경우, 이중 HASH JOIN으로 조인된 테이블의 순서를 바꿔야
할 경우가 있을 수 있다.
HASH JOIN의 경우 가장 효율적인 것은 조인순서상 작은 테이블이 먼저
조인되어 메모리상에 가급적 적은 결과값을 가진 테이블이 HASH되고
큰 테이블이 디스크상에서 HASH되어 조인되는 것이다.
이것이 반대로 될 경우는 DISK I/O가 많이 발생하고 성능이 급격하게
떨어지게 된다.

이때, ORDERED 힌트조차도 무시하고, 해당 Hash Join의 테이블 순서만
바꿀수 있는 것이 swap_join_inputs 힌트이다.
이 힌트를 통해 지정된 테이블이 Hash Join순서상 먼저 메모리에
Hash되어 조인되는 테이블이 된다.

물론 ordered 힌트를 쓰지않은 상황에서도 사용이 가능하다.

일반적으로 잘 알려지지 않은 힌트로 개인적으로 유용하게 사용하는 힌트다.

[예]

* 힌트 사용전
SQL> SELECT /*+ ORDRED */ *
FROM emp, dept, dual
WHERE emp.deptno = dept.deptno
AND dept.dname = dual.dummy;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=28588 Bytes=1486576)
1 0 HASH JOIN (Cost=17 Card=28588 Bytes=1486576)
2 1 HASH JOIN (Cost=5 Card=14 Bytes=700)
3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)
4 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=72)
5 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336)

* 힌트 사용후.
SQL> SELECT /*+ ORDERED USE_HASH(dept) SWAP_JOIN_INPUTS(dual) */ *
FROM emp, dept, dual
WHERE emp.deptno = dept.deptno
AND dept.dname = dual.dummy;

Execution Plan ----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=28588 Bytes=1486576)
1 0 HASH JOIN (Cost=17 Card=28588 Bytes=1486576)
2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336)
3 1 HASH JOIN (Cost=5 Card=14 Bytes=700)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)
5 3 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=72)

[출처] ORDERED 힌트하에서 Hash Join 테이블 조인순서 바꾸기|작성자 푸뇽