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

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

ANTI 조인은 테이블의 레코드를 추출하는 경우 조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 연산 입니다. SQL연산에서 NOT IN, NOT EXISTS, MINUS등이 해당되며 이러한 안티 조인은 MERGE ANTI-JOIN or HASH ANTI_JOIN으로 풀리도록 할 수 있는데 HASH_AJ에 대해 살펴보도록 하죠,,.,

주로 NOT IN등의 SQL문에 이용되며 힌트 구문은 다음과 같이 서브 쿼리에 명시해야 하며 서브 쿼리의 WHERE절에 NOT NULL 조건도 명시해줘야 합니다,,, 잊지 마세요!

[형식]

/*+ HASH_AJ(table) */

[9i]

SQL> SELECT ENAME, SAL

      FROM  EMP

      WHERE EMPNO IS NOT NULL

      AND    ENAME IS NOT NULL

      AND    (EMPNO, ENAME) NOT IN 

                                  (SELECT /*+ HASH_AJ */

                                           EMPNO, ENAME

                                   FROM EMP_BAK

                                   WHERE EMPNO IS NOT NULL

                                   AND    ENAME IS NOT NULL)

Execution Plan

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

SELECT STATEMENT Optimizer=CHOOSE

    HASH JOIN(ANTI)

      TABLE ACCESS (FULL) OF ‘EMP’

TABLE ACCESS (FULL) OF ‘EMP_BAK’

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

[아래의 예는 실행계획 SQL 연산(HASH ANTI-JOIN) 강좌의 일부 내용 입니다]

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

아래의 Query는 동일한 의미를 가지는 질의 입니다. 확인해 보세요~

SQL> SELECT EMPNO,

               ENAME,

               SAL

      FROM   EMP

      WHERE  (EMPNO, ENAME, SAL) NOT IN (SELECT EMPNO,

                                                         ENAME,

                                                         SAL

                                                FROM   EMP_OLD);

Execution Plan

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

0   SELECT STATEMENT Optimizer=CHOOSE

1   0   FILTER

2   1     TABLE ACCESS (FULL) OF ‘EMP’

3   1     TABLE ACCESS (FULL) OF ‘EMP_OLD’

SQL> SELECT EMPNO,

               ENAME,

               SAL

      FROM   EMP E

      WHERE  NOT EXISTS (SELECT 1 

FROM EMP_OLD EO

WHERE  EO.EMPNO = E.EMPNO

AND     EO.ENAME = E.ENAME

AND     EO.SAL    = E.SAL);

Execution Plan

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

SELECT STATEMENT Optimizer=CHOOSE

1   0   FILTER

2   1     TABLE ACCESS (FULL) OF ‘EMP’

3   1     TABLE ACCESS (FULL) OF ‘EMP_OLD’

SQL> SELECT EMPNO, ENAME, SAL  FROM   EMP

      MINUS

SELECT EMPNO, ENAME, SAL  FROM   EMP_OLD

Execution Plan

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

0   SELECT STATEMENT Optimizer=CHOOSE

1   0   MINUS

2   1     SORT (UNIQUE)

3   2        TABLE ACCESS (FULL) OF ‘EMP’

4   1     SORT (UNIQUE)

5   2        TABLE ACCESS (FULL) OF ‘EMP_OLD’

위의 세 Query중 HASH ANTI JOIN으로 풀 수 있는 것은 NOT IN을 포함하고 있는 첫번째 질의 입니다. NOT IN의 비교 대상이 되는 컬럼은 NOT NULL로 서브쿼리까지 명시해 주어야 합니다. 물론 HASH_AJ 라는 힌트 구문도 사용해야 하구요~

SQL> SELECT EMPNO,

               ENAME,

               SAL

      FROM   EMP

      WHERE  EMPNO IS NOT NULL

      AND     ENAME IS NOT NULL

      AND     SAL    IS NOT NULL

AND    (EMPNO, ENAME, SAL) 

NOT IN (SELECT /*+ HASH_AJ */ 

EMPNO,

                                    ENAME,

                                    SAL

                            FROM  EMP_OLD

WHERE  EMPNO IS NOT NULL

      AND     ENAME IS NOT NULL

      AND     SAL    IS NOT NULL);

Execution Plan

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

0   SELECT STATEMENT Optimizer=CHOOSE

1   0   HASH JOIN(ANTI)

2   1     TABLE ACCESS (FULL) OF ‘EMP’

3   1     TABLE ACCESS (FULL) OF ‘EMP_OLD’

HSH ANTI JOIN으로 풀 경우 성능이 향상되므로 위 문장과 같이 한 테이블에 존재하지 않는 로우만 추출하는 경우엔 HASH ANTI JOIN이  되도록 힌트를 사용하는 것이 유리합니다.