[Hint]조인 방법 변경(USE_HASH)
구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr
해시 조인(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 |
--------------------------------------------------------------------------------------
구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr
해시 조인(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 |
--------------------------------------------------------------------------------------
[개강임박강좌, 오프라인교육장에 오시면 보다 자세히 배울 수 있습니다.]
오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)
[주간]
[11/25]Spring3.X, MyBatis, Hibernate실무과정
[11/25]안드로이드개발자과정
[11/25]iPhone 하이브리드 앱 개발 실무과정
[12/02][기업100%환급]Spring3.X,MyBatis,PL/SQL,Oracle Hint
[12/09]초보자를위한실전SQL
[평일야간]
[11/22]자바초보에서안드로이드까지
[11/26]JAVA&WEB프레임워자실무과정
[11/26]iPhone하이브리드앱개발실무과정
[11/26]웹퍼블리싱 마스터
[11/27]SQL초보에서실전전문가까지
[11/28]Spring3.X, MyBatis, Hibernate실무과정
[주말]
[11/23]JAVA&WEB프레임워크실무과정
[11/23]웹퍼블리싱 마스터
[11/23]C#,ASP.NET마스터
[11/30]SQL초보에서전문가까지
[11/30]Spring3.X,MyBatis,Hibernate실무과정
오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)
[주간]
[11/25]Spring3.X, MyBatis, Hibernate실무과정
[11/25]안드로이드개발자과정
[11/25]iPhone 하이브리드 앱 개발 실무과정
[12/02][기업100%환급]Spring3.X,MyBatis,PL/SQL,Oracle Hint
[12/09]초보자를위한실전SQL
[평일야간]
[11/22]자바초보에서안드로이드까지
[11/26]JAVA&WEB프레임워자실무과정
[11/26]iPhone하이브리드앱개발실무과정
[11/26]웹퍼블리싱 마스터
[11/27]SQL초보에서실전전문가까지
[11/28]Spring3.X, MyBatis, Hibernate실무과정
[주말]
[11/23]JAVA&WEB프레임워크실무과정
[11/23]웹퍼블리싱 마스터
[11/23]C#,ASP.NET마스터
[11/30]SQL초보에서전문가까지
[11/30]Spring3.X,MyBatis,Hibernate실무과정
댓글 없음:
댓글 쓰기