ORACLE Tuning, [oracle hint]조인 방법 변경(HASH_AJ)

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

 

구로디지털 오엔제이프로그래밍실무교육센터

www.onjprogramming.co.kr

 

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

 

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

 

[형식]

 

SQL> SELECT ENAME, SAL

      FROM  EMP

      WHERE EMPNO IS NOT NULL

      AND    ENAME IS NOT NULL

      AND    (EMPNO, ENAME) NOT IN

                                  (SELECT

                                           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
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이  되도록 힌트를 사용하는 것이 유리합니다.

 

 

 

[실습]

 

-      실습을 위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.

http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecOrccleTun&wr_id=53&page=0&sca=&sfl=&stx=&sst=&sod=&spt=0&page=0

 

myemp1 : 1000만건

myemp1_old : 100만건

mydept : 5건

 

테스트환경 : oracle 11g

 

 

 

 

 

 

아래 세개 SQL문장은 같은 결과를 만들어 내는 동일한 SQL문이다.

 

인덱스 먼저 만들자.

 

SQL>create index idx_myemp1_old_ename_sal on myemp1_old(ename, sal)

SQL>create index idx_myemp1_ename_sal on myemp1(ename, sal)

 

먼저 not in을 사용하여 질의해 보자. (엄청 느리다… 인덱스 있어도 느리지만 정말 느리다)

 

첫번째 NOT IN을 이용한 방법

 

SQL> select empno,

  2         ename

  3  from   myemp1 e1

  4  where  (ename, sal) not in (select ename, sal

  5                                from myemp1_old e2

  6                               where e1.ename = e2.ename

  7                                 and e1.sal = e2.sal);

 

9000001 개의 행이 선택되었습니다.

 

경   과: 00:01:35.99

 

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

| Id  | Operation          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |                          |    10M|   219M|    30M  (1)|100:03:28 |

|*  1 |  FILTER            |                          |       |       |            |          |

|   2 |   TABLE ACCESS FULL| MYEMP1                   |    10M|   219M| 16971   (1)| 00:03:24 |

|*  3 |   INDEX RANGE SCAN | IDX_MYEMP1_OLD_ENAME_SAL |     1 |    18 |     3   (0)| 00:00:01 |

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

 

 

두번째 NOT EXISTS를 이용한 방법

 

 

SQL> select empno,

  2         ename

  3  from   myemp1 e1

  4  where  not exists (select 1

  5                       from myemp1_old e2

  6                      where e1.ename = e2.ename

  7                        and e1.sal = e2.sal);

 

9000001 개의 행이 선택되었습니다.

 

경   과: 00:00:52.96

-------------------------------------------------------------------------------------------------| Id  | Operation             | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |

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

|   0 | SELECT STATEMENT      |                          |  9000K|   351M|       | 36134   (1)| 00:07:1

|*  1 |  HASH JOIN RIGHT ANTI |                          |  9000K|   351M|    28M| 36134   (1)| 00:07:1

|   2 |   INDEX FAST FULL SCAN| IDX_MYEMP1_OLD_ENAME_SAL |   999K|    17M|       |  1100   (1)| 00:00:1

|   3 |   TABLE ACCESS FULL   | MYEMP1                   |    10M|   219M|       | 16961   (1)| 00:03:2

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

 

 

세번째 MINUS를 이용한 방법

 

 

SQL> select ename, sal from myemp1

  2  minus

  3  select ename, sal from myemp1_old;

 

9000001 개의 행이 선택되었습니다.

 

경   과: 00:01:22.86

 

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

| Id  | Operation           | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |            |    10M|   188M|       | 81447  (10)| 00:16:18 |

|   1 |  MINUS              |            |       |       |       |            |          |

|   2 |   SORT UNIQUE       |            |    10M|   171M|   268M| 74150   (1)| 00:14:50 |

|   3 |    TABLE ACCESS FULL| MYEMP1     |    10M|   171M|       | 16961   (1)| 00:03:24 |

|   4 |   SORT UNIQUE       |            |   999K|    17M|    26M|  7297   (1)| 00:01:28 |

|   5 |    TABLE ACCESS FULL| MYEMP1_OLD |   999K|    17M|       |  1582   (1)| 00:00:19 |

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

 

 

세가지 방법 중에서는 두번째 NOT EXISTS가 가장 빠른 결과를 보여준다. 아마도myemp1_old의 ename, sal 인덱스 덕분인 것 같다.

 

이번에는 첫번째 not in 예문을 해시 안티 조인(hash_aj)로 바꾸어 보자. 안쪽과 바깥쪽where절에 비교 대상 컬럼에 대해 is not null 비교를 해야 한다.

 

조금 빨라졌다.

 

SQL> select empno,

  2         ename

  3  from   myemp1 e1

  4  where  (ename, sal) not in (select

  5                                     ename, sal

  6                                from myemp1_old e2

  7                               where e1.ename = e2.ename

  8                                 and e1.sal = e2.sal

  9                                 and ename is not null

 10                                 and sal   is not null)

 11  and    ename is not null

 12  and    sal   is not null;

 

9000001 개의 행이 선택되었습니다.

 

경   과: 00:00:53.32

 

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

| Id  | Operation             | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |

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

|   0 | SELECT STATEMENT      |                          |  9000K|   351M|       | 36156   (1)| 00:07:14 |

|*  1 |  HASH JOIN RIGHT ANTI |                          |  9000K|   351M|    28M| 36156   (1)| 00:07:14 |

|*  2 |   INDEX FAST FULL SCAN| IDX_MYEMP1_OLD_ENAME_SAL |   999K|    17M|       |  1102   (1)| 00:00:14

|*  3 |   TABLE ACCESS FULL   | MYEMP1                   |    10M|   219M|       | 16981   (1)| 00:03:24 |

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

 

이번에는 merge anti join(merge_aj)로 바꾸어서 실행 해보자.

 

해시 안티 조인 보다는 조금 느리다.

 

SQL> select empno,

  2         ename

  3  from   myemp1 e1

  4  where  (ename, sal) not in (select

  5                                     ename, sal

  6                                from myemp1_old e2

  7                               where e1.ename = e2.ename

  8                                 and e1.sal = e2.sal

  9                                 and ename is not null

 10                                 and sal   is not null)

 11  and    ename is not null

 12  and    sal   is not null;

 

9000001 개의 행이 선택되었습니다.

 

경   과: 00:01:15.66

 

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

| Id  | Operation              | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT       |                          |  9000K|   351M|       | 92322   (1)| 00:18:28 |

|   1 |  MERGE JOIN ANTI       |                          |  9000K|   351M|       | 92322   (1)| 00:18:28 |

|   2 |   SORT JOIN            |                          |    10M|   219M|   691M| 85505   (1)| 00:17:07 |

|*  3 |    TABLE ACCESS FULL   | MYEMP1                   |    10M|   219M|       | 16981   (1)| 00:03:24 |

|*  4 |   SORT UNIQUE          |                          |   999K|    17M|    53M|  6817   (1)| 00:01:22 |

|*  5 |    INDEX FAST FULL SCAN| IDX_MYEMP1_OLD_ENAME_SAL |   999K|    17M|       |  1102   (1)| 00:00:14 

[출처] 오라클자바커뮤니티 - http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecHINT&wr_id=87