Hint]조인
방법 변경(USE_HASH)
구로디지털 오엔제이프로그래밍실무교육센터
해시 조인(Hash-Join)은 두 테이블 중 하나를 기준으로 비트맵 해시 테이블을 메모리에 올린 후 나머지 테이블을
스캔 하면서 해싱 테이블을 적용하여 메모리에 로딩된 테이블과 비교하여 매칭되는 데이터를 추출하는 방식 입니다.
성능을 위해서는 당연히 사이즈가 작은 테이블이 메모리에
올라가는 것이 좋은데 이때 이 테이블을 드라이빙 테이블(driving/outer
table) 이라고 합니다. 특히 이 해시 테이블이 메모리에 생성되면 성능은 좋으며(메모리에 생성되지 않으면 내부적으로 임시 테이블이 만들어 져야 합니다.) 두 테이블의 크기 차이가 클수록
성능은 좋아집니다.
또한 해시 조인은 안티 조인과 병렬처리와 잘 맞으며 범위
검색(Range scan)이 아닌 동등 비교(Equi-Join,
where절에서 등호로 비교하는 경우)에 더 적합 합니다.
[형식]
select … from 작은테이블, 큰테이블
[예]
아래는 Oracle 10g에서 테스트 했습니다.
아래에서 dept 테이블이 메모리에 로드되어 emp 테이블의 내용과 비교하면서 결과를 추출 합니다.
select
e.empno,
e.ename,
d.dname,
d.loc
from dept d, emp e
where e.deptno = d.deptno
---------------------------------------------------------------
Operation
Object Name Rows Bytes Cost
-------------------------------------------------------------
SELECT STATEMENT Optimizer
Mode=ALL_ROWS 14 6
HASH
JOIN 14 406 6
TABLE ACCESS
FULL SCOTT.DEPT 4 72 3
TABLE ACCESS BY
INDEX ROWID SCOTT.EMP 14 154 2
INDEX FULL
SCAN SCOTT.IDX_EMP_DEPTNO 13
1
select
e.empno,
e.ename,
d.dname,
d.loc
from emp e, dept d
where e.deptno = d.deptno
------------------------------------------------------------------
Operation
Object Name Rows Bytes Cost
---------------------------------------------------------------
SELECT STATEMENT Optimizer
Mode=ALL_ROWS 14 6
HASH
JOIN 14 406 6
TABLE ACCESS BY
INDEX ROWID SCOTT.EMP 14 154 2
INDEX FULL
SCAN SCOTT.IDX_EMP_DEPTNO 13
1
TABLE ACCESS
FULL SCOTT.DEPT 4 72 3
참고로 ALL_ROWS인 경우엔 머지 조인과 해시 조인을 비교한다면 해시
조인의 성능이 좋으며 중첩 조인의 경우 주로 첫번째 로우를 빠르게 추출하기 위한 FIRST_ROWS로 수행되는 조인 입니다.
[실습]
- 실습을
위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.
myemp1 :
1000만건
myemp1_old :
100만건
mydept :
5건
테스트환경 : oracle 11g
MYDEP1 테이블이 드라이빙 테이블
SQL> select
2 e.ename,
3 d.dname
4 from mydept1 d, myemp1 e
5 where e.deptno = d.deptno
;
20000000 개의 행이 선택되었습니다.
경 과: 00:01:42.32
------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------
| 0 |
SELECT STATEMENT | | 20M| 1525M| 17043 (2)| 00:03:25
|
|* 1 |
HASH JOIN | | 20M| 1525M| 17043 (2)| 00:03:25
|
| 2
| TABLE ACCESS FULL| MYDEPT1 | 10 | 650
| 3 (0)| 00:00:01 |
| 3 |
TABLE ACCESS FULL| MYEMP1 | 10M| 143M| 16941 (1)| 00:03:24
|
------------------------------------------------------------------------------
이번에는 MYEMP1 테이블이 드라이빙 테이블이 된다.
SQL> select
2 e.ename,
3 d.dname
4 from myemp1 e, mydept1 d
5 where e.deptno = d.deptno
;
20000000 개의 행이 선택되었습니다.
경 과: 00:02:03.14
--------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------
| 0 |
SELECT STATEMENT | | 20M| 1525M| | 29883 (1)| 00:05:59
|
|* 1 |
HASH JOIN | | 20M| 1525M| 257M| 29883 (1)| 00:05:59
|
| 2 |
TABLE ACCESS FULL| MYEMP1 | 10M| 143M| | 16941 (1)| 00:03:24
|
| 3 |
TABLE ACCESS FULL| MYDEPT1 | 10 | 650 | | 3 (0)| 00:00:01
|
--------------------------------------------------------------------------------------
