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 |
------------------------------------------------------------------------------
오라클자바커뮤니티에서 운영, 개발자 전문교육, 개인80%환급 오엔제이프로그래밍실무교육센터(www.onjprogramming.co.kr)
평일주간(9:30~18:30) 개강
(4/15)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(4/21)C#4.0,ADO.NET,Network 프로그래밍
(4/21)[기업100%환급]SQL기초에서 Schema Object까지
(4/21)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
(4/28)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
평일야간(19:00~22:00) 개강
(4/14)웹퍼블리싱 마스터
(4/15)Spring3.X, MyBatis, Hibernate실무과정
(4/15)C#,ASP.NET마스터
(4/15)SQL초보에서실전전문가까지
(4/22)안드로이드개발자과정
주말(10:00~18:00) 개강
(4/19)웹퍼블리싱 마스터
(4/19)SQL초보에서실전전문가까지
(4/19)C#,ASP.NET마스터
(5/03)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(5/03)Spring3.X, MyBatis, Hibernate실무과정
(5/03)안드로이드개발자과정
평일주간(9:30~18:30) 개강
(4/15)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(4/21)C#4.0,ADO.NET,Network 프로그래밍
(4/21)[기업100%환급]SQL기초에서 Schema Object까지
(4/21)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
(4/28)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
평일야간(19:00~22:00) 개강
(4/14)웹퍼블리싱 마스터
(4/15)Spring3.X, MyBatis, Hibernate실무과정
(4/15)C#,ASP.NET마스터
(4/15)SQL초보에서실전전문가까지
(4/22)안드로이드개발자과정
주말(10:00~18:00) 개강
(4/19)웹퍼블리싱 마스터
(4/19)SQL초보에서실전전문가까지
(4/19)C#,ASP.NET마스터
(5/03)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(5/03)Spring3.X, MyBatis, Hibernate실무과정
(5/03)안드로이드개발자과정
댓글 없음:
댓글 쓰기