GaramX

사용자 도구

사이트 도구

현재 위치: home » oracle » hashjoin
추적: • hashjoin • decorator • variable • material • binding • split_to_table
oracle:hashjoin

차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

차이 보기로 링크

양쪽 이전 판이전 판
oracle:hashjoin [2023/11/16 08:17] – taekguoracle:hashjoin [2025/04/15 10:05] (현재) – 바깥 편집 127.0.0.1
줄 1: 줄 1:
 +====== 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 |
 +------------------------------------------------------------------------------

문서 도구