[오라클조인]해시조인(Hash Join),use_hash, ordered, Build Input,
Probe,Oracle Join ,오라클힌트란,HINT교육강좌,힌트카페,힌트블로그, 오라클튜닝개요
Hash Join
해시 조인(Hash-Join)은 두 테이블 중 하나를 기준으로 비트맵 해시 테이블을 메모리에 올린 후 나머지 테이블을
스캔 하면서 해싱 테이블을 적용하여 메모리에 로딩된 테이블과 비교하여 매칭되는 데이터를 추출하는 방식의 조인이다.
RDBMS에 서
비용이 가장 많이 들어가는 Join 방법으로 주로 작은 Table과 큰 Table 의 Join 시 사용되어 지며 , Driving 조건과
상관없이 좋은 성능을 발휘할 수 있다.
[처리순서]
1. 작은 테이블(Build Input)을 읽어 Hash
Area에 해시 테이블 생성한다.
2. 큰테이블 집합(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인하는 방식이다.
[특징]
- 해시 조인은 안티 조인과 병렬처리와 잘 맞으며 범위 검색(Range scan)이 아닌 동등
비교(Equi-Join, where절에서 등호로 비교하는 경우)에 더 적합하다.
- NL조인 과 달리 Random 액세스 부하가
없다.(단, 양쪽집합을 읽는 과정에서 인덱스를 이용한다면 Random 액세스 발생)
- NL조인 과 달리 Hash Area에 미리
생성해 둔 해시 테이블(또는 해시 맵)을 이용한다.
(해시테이블을 만드는 단계는 전체범위처리 불가피, Probe Input을 스캔하는
단계는 NL조인처럼 부분범위처리가능)
- 소트머지조인과 달리 조인 전에 미리 양쪽 집합을 정렬하는 부담이 없으며 NL조인 과 달리
래치획득 과정없이 PGA에서 빠르게 데이터 탐색한다.
- 해시 테이블을 생성하는 비용이 수반됨으로 Build Input이 작을때
효과적이며 PGA(or SGA) 메모리에 할당되는 Hash Area에 담길 정도로 충분히 작아야 한다. 해시키 값으로 사용되는 컬럼에 중복값이
거의 없을 때 효과적이다.
- SQL 문장에서 옵티마이저는 해쉬 조인으로 수행하기 위해 작은 테이블을 메모리에 로드 한 후
큰 테이블을 여러 Partition으로 분리하여 메모리에 로드가 되어 있는 작은 테이블을 해쉬 알고리즘에 의하여 탐색하게 되고 여러
Partition 으로 나뉘어 지는 테이블은 HASH_AREA_SIZE 에 명시된 메모리에 상주되며 메모리가 충분치 않아 메모리에 모두 상주
시킬수 있는 상황이라면 디스크에 위치하게 된다. (충분한 hash_area_size 제공필요)
- 해시조인을 사용하기 위해서는
USE_HASH hint를 사용 한다.
해시 조인 사용기준
- 기준
1. 한쪽 테이블이 Hash
Area에 담길 정도로 충분히 작아야함.
2. Build Input 해시 키 컬럼에 중복 값이 거의 없어야 함.
-
조건
1. 조인 컬럼에 적당한 인덱스가 없어 NL조인이 비효율적일때
2. 조인 컬럼에 인덱스가 있더라고 NL 조인 드라이빙
집합에서 Inner 쪽 집합으로서의 조인 액세스량이 많아 Random 액세스 부하가 심할 때
3. 소트 머지 조인하기에는 두 테이블이
너무 커 소트 부하가 심할때
4. 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할때
- 결과
1. 해시테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조이다.
2. 수행빈도가 낮고, 쿼리수행
시간이 오래 걸리는, 대용량테이블을 조인할 때
[예제]
실습을 위한 데이터는 아래 URL에서 만들자.
http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecHINT&wr_id=117
myemp1 : 2,000만건
mydept1 : 5건(0,1,2,3,4)
아래 실습은
Oracle11g R2에서 진행되었다.
SQL> set autotrace on
SQL> set timing on
[myemp1의 deptno 컬럼에 인덱스가 없는 경우]
SQL> select /*+
use_hash(d e) */ e.empno, e.ename, d.dname
2 from mydept1 d, myemp1 e
3 where e.deptno = d.deptno
4 and e.deptno = 0;
4000000
개의 행이 선택되었습니다.
경 과: 00:00:33.65
Execution Plan
----------------------------------------------------------
Plan hash
value: 2850841177
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4000K| 129M| 40081 (1)| 00:08:01 |
|* 1 | HASH JOIN | | 4000K| 129M| 40081 (1)| 00:08:01 |
|* 2 | TABLE ACCESS FULL| MYDEPT1 | 1 | 10 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| MYEMP1 | 4000K| 91M| 40057 (1)| 00:08:01 |
------------------------------------------------------------------------------
SQL> select /*+ use_hash(e) */ e.empno, e.ename, d.dname
2 from mydept1 d, myemp1 e
3 where e.deptno = d.deptno
4
and e.deptno = 0;
4000000 개의 행이 선택되었습니다.
경 과: 00:00:35.82
Execution Plan
----------------------------------------------------------
Plan hash
value: 934128101
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4000K| 129M| 40061 (1)| 00:08:01 |
| 1 | MERGE JOIN | | 4000K| 129M| 40061 (1)| 00:08:01 |
|* 2 | TABLE ACCESS FULL | MYEMP1 | 4000K| 91M| 40057 (1)| 00:08:01
|
|* 3 | SORT JOIN | | 1 | 10 | 4 (25)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| MYDEPT1 | 1 | 10 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
아래처럼 테이블 하나만 쓸려면 ordered와 같이 사용되야 한다.
SQL> select /*+
ordered use_hash(e) */ e.empno, e.ename, d.dname
2 from mydept1 d,
myemp1 e
3 where e.deptno = d.deptno
4 and e.deptno = 0;
4000000 개의 행이 선택되었습니다.
경 과: 00:00:33.90
Execution Plan
----------------------------------------------------------
Plan hash
value: 2850841177
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4000K| 129M| 40081 (1)| 00:08:01 |
|* 1 | HASH JOIN | | 4000K| 129M| 40081 (1)| 00:08:01 |
|* 2 | TABLE ACCESS FULL| MYDEPT1 | 1 | 10 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| MYEMP1 | 4000K| 91M| 40057 (1)| 00:08:01 |
------------------------------------------------------------------------------
[myemp1의 deptno 컬럼에 인덱스가 있는 경우]
먼저 Myemp1
테이블의 deptno 컬럼에 비트맵 인덱스를 만들자.
SQL> --비트맵 인덱스를 만들자.
SQL> create
bitmap index idx_myemp1_deptno on myemp1(deptno);
인덱스가 생성되었습니다.
경 과: 00:00:24.32
SQL> select /*+ use_hash(d e)
index_combine(e)*/ e.empno, e.ename, d.dname
2 from mydept1 d, myemp1 e
3 where e.deptno = d.deptno
4 and e.deptno = 0;
4000000
개의 행이 선택되었습니다.
경 과: 00:00:55.20
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4000K| 129M|
705
54 (1)| 00:14:07 |
|* 1 | HASH JOIN |
| 4000K| 129M| 705
54 (1)| 00:14:07 |
|* 2 | TABLE ACCESS
FULL | MYDEPT1 | 1 | 10 |
3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | MYEMP1 | 4000K| 91M|
705
31 (1)| 00:14:07 |
| 4 | BITMAP CONVERSION TO ROWIDS|
| | |
|* 5 | BITMAP INDEX SINGLE VALUE |
BIDX_MYEMP1_DEPTNO | | |
실망스러운 결과다.
이번에는
B*Tree 인덱스를 만들어 보자.
SQL> create index idx_myemp1_deptno on
myemp1(deptno);
인덱스 범위 검색으로 별루다.
SQL> select /*+ use_hash(d
e) index(e idx_myemp1_deptno) */ e.empno, e.ename, d.dname
2 from mydept1
d, myemp1 e
3 where e.deptno = d.deptno
4 and e.deptno = 0;
4000000 개의 행이 선택되었습니다.
경 과: 00:01:07.43
| 0 |
SELECT STATEMENT | | 4000K| 129M| 116
K (1)|
00:23:13 |
|* 1 | HASH JOIN | | 4000K|
129M| 116
K (1)| 00:23:13 |
|* 2 | TABLE ACCESS FULL |
MYDEPT1 | 1 | 10 | 3
(0)| 00:00:01 |
| 3 | TABLE
ACCESS BY INDEX ROWID| MYEMP1 | 4000K| 91M| 115
K (1)|
00:23:12 |
|* 4 | INDEX RANGE SCAN | IDX_MYEMP1_DEPTNO |
4000K| | 7732
(1)| 00:01:33 |
이번에는 드라이빙 테이블을 myemp1으로
바꾸어 보자.
Use_hash의 인자로 비드라이빙 테이블이 오고 힌트 맨 앞에는 ordered를 써주자. 물론 from절 바로
다음에 드라이빙 테이블을 기술하자.(ordered를 기술하면 from절 다음에 반드시 드라이빙 테이블이 와야한다)
SQL>
select /*+ ordered use_hash(d) */ e.empno, e.ename, d.dname
2 from myemp1
e, mydept1 d
3 where e.deptno = d.deptno
4 and e.deptno = 0;
4000000 개의 행이 선택되었습니다.
경 과: 00:00:57.92
Execution Plan
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4000K| 129M| | 46920 (1)| 00:0
9:24 |
|* 1 | HASH JOIN | | 4000K| 129M| 137M| 46920
(1)| 00:0
9:24 |
|* 2 | TABLE ACCESS FULL| MYEMP1 | 4000K| 91M|
| 40057 (1)| 00:0
8:01 |
|* 3 | TABLE ACCESS FULL| MYDEPT1 | 1
| 10 | | 3 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
이번에는 leading힌트를 사용해 보자. From절 다음에 꼭 드라이빙 테이블이 와야 하는 것은 아니다.
SQL> select /*+ leading(d) use_hash(e) */ e.empno, e.ename, d.dname
2 from myemp1 e, mydept1 d
3 where e.deptno = d.deptno
4
and e.deptno = 0;
4000000 개의 행이 선택되었습니다.
경 과: 00:00:33.48
Execution Plan
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4000K| 129M| 40081 (1)| 00:08:01 |
|* 1 | HASH JOIN | | 4000K| 129M| 40081 (1)| 00:08:01 |
|* 2 | TABLE ACCESS FULL| MYDEPT1 | 1 | 10 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| MYEMP1 | 4000K| 91M| 40057 (1)| 00:08:01 |
------------------------------------------------------------------------------
Leading 힌트에서는 from절 다음 테이블의 위치는 중요하지 않다.
SQL> select
/*+ leading(d) use_hash(e) */ e.empno, e.ename, d.dname
2 from mydept1 d,
myemp1 e
3 where e.deptno = d.deptno
4 and e.deptno = 0;
4000000 개의 행이 선택되었습니다.
경 과: 00:00:33.61
Execution Plan
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4000K| 129M| 40081 (1)| 00:08:01 |
|* 1 | HASH JOIN | | 4000K| 129M| 40081 (1)| 00:08:01 |
|* 2 | TABLE ACCESS FULL| MYDEPT1 | 1 | 10 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| MYEMP1 | 4000K| 91M| 40057 (1)| 00:08:01 |
------------------------------------------------------------------------------
 |
[기업100%환급]Spring ,MyBatis,Hibernate실무과정  |
총 |
5일 |
40시간 |
|
02-24 |
[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지  |
총 |
5일 |
40시간 |
|
03-03 |
 |
| Spring3.X, MyBatis, Hibernate실무과정 |
총 |
12일 |
36시간 |
|
03-03 |
| 자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 |
총 |
24일 |
72시간 |
|
03-14 |
 |
| 자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 |
총 |
10일 |
70시간 |
|
03-01 |
| [주말저녁]자바기초에서JSP,Servlet,Ajax,jQUERY,스프링,마이바티스,하이버네이트 |
총 |
18일 |
72시간 |
|
03-01 |
| Spring3.X, MyBatis, Hibernate실무과정 |
총 |
5일 |
35시간 |
|
03-09 |
