IT실전교육,탑크리에듀교육센터: 오라클힌트

레이블이 오라클힌트인 게시물을 표시합니다. 모든 게시물 표시

2013년 8월 3일 토요일

[Oracle SQL Explain Plan]ORACLE HINT 강좌 실행계획 SQL 연산(HASH ANTI-JOIN)

실행계획 SQL 연산(HASH ANTI-JOIN

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

www.onjprogramming.co.kr

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

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

create table myemp1

(empno number not null primary key,

ename varchar2(100),

deptno number,

addr varchar2(100),

sal number

)

-- 실습을 위해 myemp1을 1000만건 만들자.

DECLARE

v_c NUMBER := 1;

BEGIN

WHILE (v_c <= 10000000) LOOP

insert into myemp1 values ( v_c, '홍길동'||v_c, mod(v_c, 5), '서울'||v_c, mod(v_c, 1000000));

v_c := v_c + 1;

insert into myemp1 values ( v_c, '다길동'||v_c, mod(v_c, 5), '부산'||v_c, mod(v_c, 1000000));

v_c := v_c + 1;

insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '대구'||v_c, mod(v_c, 1000000));

v_c := v_c + 1;

insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '광주'||v_c, mod(v_c, 1000000));

v_c := v_c + 1;

END LOOP;

commit;

END;

create table myemp1_old

as select * from myemp1 where rownum < 1000000

-- 통계정보 셍성

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'MYDEPT1_OLD')

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'MYDEPT1')

별다른 인덱스가 없는 상태에서 실행해 보자. 37초 걸린다.

오라클 11g에서 기본적으로 merge anti joinm으로 실행계획을 잡는다.

SQL> select count(e1.ename)

2 from myemp1 e1

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

4 from myemp1_old e2);

COUNT(E1.ENAME)--28

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

9000001

경 과: 00:00:37.87

Execution Plan

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

| 0 | SELECT STATEMENT | | 1 | 82 | | 90492

| 1 | SORT AGGREGATE | | 1 | 82 | |

| 2 | MERGE JOIN ANTI NA | | 10M| 782M| | 90492

| 3 | SORT JOIN | | 10M| 162M| 536M| 72263

| 4 | TABLE ACCESS FULL| MYEMP1 | 10M| 162M| | 16961

|* 5 | SORT UNIQUE | | 1070K| 66M| 156M| 18229

| 6 | TABLE ACCESS FULL| MYEMP1_OLD | 1070K| 66M| |

이번에는 HASH ANTI JOIN으로 힌트를 주고 실행하자. ANTI JOIN 일때는 where절의 not in 출현 컬럼에 대해 is not null 조건을 주도록 하자.

SQL> select

count(e1.ename)

from myemp1 e1

where (ename, sal) not in (select

ename, sal

from myemp1_old e2

where ename is not null

and sal is not null)

and ename is not null

and sal is not null

실행해 보면 수행시간이 절반 이상으로 준다. MERGE ANTI JOIN 보다 HASH ANTI JOIN 성능이 낫다.

COUNT(E1.ENAME)

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

9000001

경 과: 00:00:14.92

Execution Plan

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

| 0 | SELECT STATEMENT | | 1 | 82 | | 36297

| 1 | SORT AGGREGATE | | 1 | 82 | |

|* 2 | HASH JOIN RIGHT ANTI| | 10M| 782M| 78M| 36297

|* 3 | TABLE ACCESS FULL | MYEMP1_OLD | 1070K| 66M| |

|* 4 | TABLE ACCESS FULL | MYEMP1 | 10M| 162M| |

SQL> SELECT count(ename)

2 FROM MYEMP1 E

3 WHERE NOT EXISTS (SELECT 1

4 FROM MYEMP1_OLD EO

5 WHERE EO.ENAME = E.ENAME

6 AND EO.SAL = E.SAL)

7 and ename is not null

8 and sal is not null;

COUNT(ENAME)

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

9000001

경 과: 00:00:11.56

Execution Plan

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

| 0 | SELECT STATEMENT | | 1 | 82 | | 36295

| 1 | SORT AGGREGATE | | 1 | 82 | |

|* 2 | HASH JOIN RIGHT ANTI| | 10M| 782M| 78M| 36295

| 3 | TABLE ACCESS FULL | MYEMP1_OLD | 1070K| 66M| |

|* 4 | TABLE ACCESS FULL | MYEMP1 | 10M| 162M| |

이번에는 HASH_AJ 힌트를 사용해 보자.

수행 시간은 대략 비슷하다.

SQL> SELECT count(ename)

2 FROM MYEMP1 E

3 WHERE NOT EXISTS (SELECT /*+ hash_aj */1

4 FROM MYEMP1_OLD EO

5 WHERE EO.ENAME = E.ENAME

6 AND EO.SAL = E.SAL)

7 and ename is not null

8 and sal is not null;

COUNT(ENAME)

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

9000001

경 과: 00:00:11.00

Execution Plan

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

| 0 | SELECT STATEMENT | | 1 | 82 | | 36295

| 1 | SORT AGGREGATE | | 1 | 82 | |

|* 2 | HASH JOIN RIGHT ANTI| | 10M| 782M| 78M| 36295

| 3 | TABLE ACCESS FULL | MYEMP1_OLD | 1070K| 66M| |

|* 4 | TABLE ACCESS FULL | MYEMP1 | 10M| 162M| |

이번에는 MINUS로 풀어 보자.

SQL> with a as (

2 select ename, sal

3 from myemp1

4 minus

5 select ename, sal

6 from myemp1_old

7 )

8 select count(ename) from a ;

COUNT(ENAME)

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

9000001

경 과: 00:00:30.48

Execution Plan

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

| 0 | SELECT STATEMENT | | 1 | 52 | | 90492

| 1 | SORT AGGREGATE | | 1 | 52 | |

| 2 | VIEW | | 10M| 495M| | 90492

| 3 | MINUS | | | | |

| 4 | SORT UNIQUE | | 10M| 162M| 268M| 72263

| 5 | TABLE ACCESS FULL| MYEMP1 | 10M| 162M| |

| 6 | SORT UNIQUE | | 1070K| 66M| 78M| 18229

| 7 | TABLE ACCESS FULL| MYEMP1_OLD | 1070K| 66M| |

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

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

이번에는 조금 더 개선을 해서 EMPTEST TABLE의 EMPNO, ENAME, SAL 컬럼으로 비트맵 인덱스를 구성해서 쿼리를 해 보자.

create bitmap index idx_bitmap_empno_ename_sal on emptiest (empno, ename, sal)

select /*+ index(idx_bitmap_empno_ename_sal e1) */

count(e1.ename)

from emptest e1

where (ename, sal) not in (select

ename, sal

from emptest_old e2

where ename is not null

and sal is not null)

and ename is not null

and sal is not null

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

[Oracle SQL Hint]오라클힌트-실행계획 SQL연산(MERGE SEMI JOIN), ORACLE HINT

실행계획 SQL연산(MERGE SEMI JOIN)

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

www.onjprogramming.co.kr

SEMI JOIN은 첫 번째 매칭되는 value를 찾기만 하면 결과를 돌려주는 join 문입니다.

MERGE SEMI JOIN과 HASH SEMI JOIN으로 나타나는데 EXISTS와 같은 Query에서 서브 쿼리 조건에 대한 인덱스가 존재하지 않으면 비효율적인 실행 계획이 세워지게 되는데 이러한 경우 세미 조인을 적절히 이용하면 됩니다.

아래 예문을 따라 해 보면서 각각의 경우 실행계획 및 수행 시간을 유심히 보시기 바랍니다. 기본적으로 오라클 11g에서 MERGE 보다는 HASH 조인을 우선적으로 선택 함을 할 수 있습니다.

SQL> select index_name, table_name from user_indexes

2 where table_name like 'EMPTEST'

3 /

INDEX_NAME TABLE_NAME

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

IDX_EMPTEST_ADDR EMPTEST

IDX_EMPTEST_DEPTNO EMPTEST

실습을 위해 DEPTNO 컬럼의 인덱스를 숨기자

(오라클 옵티마이저가 사용하지 않도록… )

SQL> alter index IDX_EMPTEST_DEPTNO invisible;

인덱스가 변경되었습니다.

-- CBO로 동작하는 경우 서브조인에서 exists등이 인덱스가 없는 경우라면 어떻게 동작하는지 보자

SQL> select count(e.ename)

2 from emptest e

3 where exists (select 1

4 from depttest d

5 where e.deptno = d.deptno);

COUNT(E.ENAME)

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

2500000

경 과: 00:00:02.34

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

| 0 | SELECT STATEMENT

| 1 | SORT AGGREGATE

|* 2 | HASH JOIN RIGHT SEMI|

| 3 | TABLE ACCESS FULL | DEPTTEST

| 4 | TABLE ACCESS FULL | EMPTEST

-- 기본적으로 전체 테이블 FULL SCAN 후 HASH SEMI 조인으로 수행된다.

-- 이번엔 RULE 힌트를 넣어 RBO로 동작하도록 하고 결과를 보자.

SQL> select count(e.ename)

2 from emptest e

3 where exists (select 1

4 from depttest d

5 where e.deptno = d.deptno);

COUNT(E.ENAME)

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

2500000

Execution Plan

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

| 0 | SELECT STATEMENT

| 1 | SORT AGGREGATE

|* 2 | FILTER

| 3 | TABLE ACCESS FULL| EMPTEST

|* 4 | TABLE ACCESS FULL| DEPTTEST |

이번에는 HASH SEMI JOIN 힌트 구문을 이용해 보자.

당연히 해시 세미조인을 이용할 것이다.

SQL> select count(e.ename)

2 from emptest e

3 where exists (select 1

4 from depttest d

5 where e.deptno = d.deptno);

COUNT(E.ENAME)

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

2500000

경 과: 00:00:02.32

Execution Plan

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

| 0 | SELECT STATEMENT | | 1 | 20 | 4172 (2)|

| 1 | SORT AGGREGATE | | 1 | 20 | |

|* 2 | HASH JOIN RIGHT SEMI| | 2500K| 47M| 4172 (2)|

| 3 | TABLE ACCESS FULL | DEPTTEST | 5 | 15 | 3 (0)|

| 4 | TABLE ACCESS FULL | EMPTEST | 2500K| 40M| 4156 (1)|

이번엔 MERGR SEMI JOIN으로… 해시 세미 조인 보다는 시간이 좀 더 걸린다.

SQL> select count(e.ename)

2 from emptest e

3 where exists (select 1

4 from depttest d

5 where e.deptno = d.deptno);

COUNT(E.ENAME)

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

2500000

경 과: 00:00:03.50

Execution Plan

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

| 0 | SELECT STATEMENT | | 1 | 20 | | 17977

| 1 | SORT AGGREGATE | | 1 | 20 | | |

| 2 | MERGE JOIN SEMI | | 2500K| 47M| | 17977

| 3 | SORT JOIN | | 2500K| 40M| 134M| 17973

| 4 | TABLE ACCESS FULL| EMPTEST | 2500K| 40M| | 4156

|* 5 | SORT UNIQUE | | 5 | 15 | | 4 (25)| 0

| 6 | TABLE ACCESS FULL| DEPTTEST | 5 | 15 | | 3

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

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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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