실행계획 SQL 연산(HASH
ANTI-JOIN
구로디지털 오엔제이프로그래밍실무교육센터
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
