해시 조인(Hash-Join)은 두 테이블 중 하나를 기준으로 비트맵 해시 테이블을 메모리에 올린 후 나머지 테이블을 스캔 하면서 해싱 테이블을 적용하여 메모리에 로딩된 테이블과 비교하여 매칭되는 데이터를 추출하는 방식 입니다.
성능을 위해서는 당연히 사이즈가 작은 테이블이 메모리에 올라가는 것이 좋은데 이때 이 테이블을 드라이빙 테이블(driving/outer table) 이라고 합니다. 특히 이 해시 테이블이 메모리에 생성되면 성능은 좋으며(메모리에 생성되지 않으면 내부적으로 임시 테이블이 만들어 져야 합니다.) 두 테이블의 크기 차이가 클수록 성능은 좋아집니다.
또한 해시 조인은 안티 조인과 병렬처리와 잘 맞으며 범위 검색(Range scan)이 아닌 동등 비교(Equi-Join, where절에서 등호로 비교하는 경우)에 더 적합 합니다.
[형식]
/*+ USE_HASH ( table [table]... ) */
select /*+ ordered use_hash(큰테이블) */ … from 작은테이블, 큰테이블
[예]
아래는 Oracle 10g에서 테스트 했습니다.
아래에서 dept 테이블이 메모리에 로드되어 emp 테이블의 내용과 비교하면서 결과를 추출 합니다.
select /*+ ORDERED USE_HASH(e) */
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 /*+ ORDERED USE_HASH(d) */
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로 수행되는 조인 입니다.
[실습]
- 실습을 위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.
http://www.oraclejavanew.kr/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
MYDEP1 테이블이 드라이빙 테이블
SQL> select /*+ ORDERED USE_HASH(e) */
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 /*+ ORDERED USE_HASH(d) */
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 |
--------------------------------------------------------------------------------------
 | |||||
|---|---|---|---|---|---|
[기업100%환급]SQL기초에서
Schema Object까지  |
총 | 5일 | 40시간 | 02-10 | |
[기업100%환급]PL/SQL,ORACLE
HINT,TUNING  |
총 | 5일 | 40시간 | 02-17 | |
 | |||||
| SQL초보에서실전전문가까지
|
총 | 18일 | 54시간 | 02-11 | |
 | |||||
| SQL초보에서실전전문가까지
|
총 | 8일 | 56시간 | 02-22 | |
댓글 없음:
댓글 쓰기