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

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

세미 조인은 보통 EXISTS를 사용하는 서브쿼리의 형태로 나타나며 이러한 경우 서브 쿼리에 인덱스가 존재하지 않는다면 상당히 비효율적인데 이러한 서브 쿼리에 인덱스가 없는 경우 SEMI-JOIN이 일어나도록 유도한다면 성능의 향상을 꽤할 수 있습니다. 즉 인덱스가 없는 EXISTS를 사용하는 쿼리라면 HASH_SJ or MERGE_SJ 힌트 구분을 이용해서 세미 조인이 일어나도록 푸는 것이 좋습니다.

주로 서브 쿼리의 컬럼에 인덱스가 존재하지 않는 Correlated Exists 쿼리에 사용되는 힌트 구문으로 세미조인을 해시 조인을 이용하도록 하는 힌트 구문으로 Outer Table의 ROW수가 많은 경우에 더 효율적 입니다.

아래의 질의는 EMP 테이블에서 부서에 속해있는 직원들의 이름, 급여를 출력하는 예입니다. 

(현시점에 DEPT TABLE의 DEPTNO는 인덱스가 존재한다고 가정하겠습니다)

SQL>SELECT ENAME, SAL

     FROM   EMP E

     WHERE  EXISTS ( SELECT 1

                        FROM   DEPT D

                        WHERE  E.DEPTNO = D.DEPTNO);

Execution Plan

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

0   SELECT STATEMENT Optimizer=CHOOSE

1   0 FILTER

2   1   TABLE ACCESS (FULL) OF ‘EMP’

3   1   INDEX (RANGR SCAN) OF ‘idx_dept_deptno’ (NON-UNIQUE)

다음과 같이 인덱스를 제거한 후 다시 위의 쿼리를 실행해 보면…

SQL>drop index idx_dept_deptno;

SQL>SELECT ENAME, SAL

     FROM   EMP E

     WHERE  EXISTS ( SELECT 1

                        FROM   DEPT D

                        WHERE  E.DEPTNO = D.DEPTNO);

Execution Plan

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

0   SELECT STATEMENT Optimizer=CHOOSE

1   0 FILTER

2   1   TABLE ACCESS (FULL) OF ‘EMP’

3   1   TABLE ACCESS (FULL) OF ‘DEPT’

위의 경우 서브쿼리의 조건 컬럼에 인덱스가 없으므로 EMP 테이블의 한 건에 대해 DEPT 테이블을 계속 FULL SCAN 해나가므로 성능이 떨어지게 됩니다. 이를 세미 조인이 일어나도록 유도한 SQL문은 다음과 같습니다.

SQL>SELECT ENAME, SAL

     FROM   EMP E

     WHERE  EXISTS ( SELECT /*+ HASH_SJ */ 1

                        FROM   DEPT D

                        WHERE  E.DEPTNO = D.DEPTNO);

Execution Plan

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

0   SELECT STATEMENT Optimizer=CHOOSE

1   0 SORT(AGGREGATE)

2   1   HASH JOIN (SEMI)

3   2     TABLE ACCESS (FULL) OF ‘EMP’

4   2     TABLE ACCESS (FULL) OF ‘DEPT’