해시 조인
NL 조인은 인덱스를 이용한 조인 방식임으로 인덱스 구성에 따른 성능 차이가 심하다 인덱스를 아무리 완벽하게 구성해도 랜덤 I/O 때문에 대량 데이터 처리에 불리하고 버퍼캐시 히트율에 따라 들쭉날쭉한 성능을 보인다 소트 머지와 해시 머지는 인덱스를 이용하지 않기 때문에 일정한 성능을 보인다
소트 머지 단점
앞에서 말했지만 소트 머지는 기본적으로 정렬을 하고 머지를 하는 점이라서 일단 대량 데이터를 정렬하는데 많은 코스트가 들어간다 하지만 해시 조인은 그런 부담도 없지만 그렇다고 모든 조인을 해시 조인으로 만 해결할 수는 없는 것이다 적절하게 조인을 선택하는 것도 중요하다
해시 조인 기본 메커니즘
Build 단계 작은 쪽 테이블을 읽어 해시 테이블을 생성한다
Probe 단계 큰 쪽 테이블을 읽어서 해시 테이블을 탐색하면서 조인한다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
EXPLAIN PLAN FOR
SELECT /*+ ordered use_hash(c)*/
* FROM Ex19_emp e INNER JOIN Ex19_custom c ON e.emp_no = c.emp_code
WHERE e.join_date BETWEEN '19950101' AND '19992131'
AND e.emp_dept_cd >= 20
AND c.custom_sal_amt > 3000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------------------------------+
Plan hash value: 3597589949 |
|
---------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 4714 | 865K| 121 (0)| 00:00:01 ||
|* 1 | HASH JOIN | | 4714 | 865K| 121 (0)| 00:00:01 ||
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EX19_EMP | 2 | 130 | 1 (0)| 00:00:01 ||
|* 3 | INDEX RANGE SCAN | EX19_EMP_IDX | 2 | | 1 (0)| 00:00:01 ||
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EX19_CUSTOM | 23568 | 2830K| 120 (0)| 00:00:01 ||
|* 5 | INDEX RANGE SCAN | EX19_CUSTOM_IDX2 | 23568 | | 120 (0)| 00:00:01 ||
---------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("E"."EMP_NO"="C"."EMP_CODE") |
2 - filter(TO_NUMBER("E"."EMP_DEPT_CD")>=20) |
3 - access("E"."JOIN_DATE">='19950101' AND "E"."JOIN_DATE"<='19992131') |
5 - access("C"."CUSTOM_SAL_AMT">3000) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
참고로 해시 힌트는 use_hash를 사용합니다
- Build 단계
1
2
3
4
5
6
SELECT *
FROM Ex19_emp
WHERE join_date BETWEEN '19950101' AND '19992131'
AND emp_dept_cd >= 20
이 조건에 대항하는 Ex19_emp 테이블을 읽어서 해시 테이블을 생성합니다 이때 조인 칼럼인 emp_no를 해시 테이블 key 값으로 생성합니다 즉 emp_no 값을 해시 함수에 넣어서 반환된 값을 해시 체인을 갖고 그 해시 테인에 데이터를 연결합니다 해시 테이블은 소트 머지 테이블과 마찬가지로 PGA 영역에 보관되는데 그중에서 HASH AREA에 보관됩니다 마찬가지로 전부 보관을 못 하게 된다면 Temp 테이블 스페이스에 저장됩니다
이렇게 말이죠
- Probe 단계
아래 조건에 해당되는 Ex19_custom 데이터를 하나씩 읽어 앞서 생성한 해시 테이블을 탐색합니다 즉 c.emp_code를 같은 해시 함수에 넣어서 나온 값하고 일치하는 값을 찾아서 일치하면 조인을 해서 결과 셋에 저장을 하고 그렇지 않으면 넘어가게 됩니다
1
2
3
4
SELECT * FROM Ex19_custom
WHERE custom_sal_amt > 3000
이 결과는 위에 테이블은 Ex19_emp 테이블의 해시테이블입니다 그리고 아래는 Ex19_custom 해시 함수 반환값입니다 해시 테이블 (X) 여기서 일치하는 주황색만 결과 셋에 반영되고 나머지는 조인 실패로 들어갑니다
해시조인이 빠른 이유
Hash Area에 생성한 해시 테이블을 이용한다는 점만 다를 뿐 해시 조인도 조인 프로세싱 자체는 NL 조인하고 같다 하지만 NL 조인보다 빠른 이유는 인덱스 기반이 아니기도 하고 PGA 영역에 데이터를 저장하다 보니 SGA에 저장해서 얻어오는 래치 메커니즘이 필요가 없기 때문이다
조인 메서드 선택 기준
우리는 이제까지 조인을 3가지 배웠다 NL 조인 소트 머지 조인 해시 조인 이 중에서 적절한 것을 고르는 것은 여간 쉽지 않다 그냥 제일 무난 무난한 해시 조인으로 통일하는 게 좋을 수도 있겠지만 다음을 잘 고려해서 적절한 조인 메서드를 사용해 보자
적은 데이터를 조인 NL 조인
대량 데이터를 조인할 때 해시 조인
대량 데이터 조인인데 해시 조인을 사용할 수 없는 경우 조인 조건식이 동치식이 아닐 때 소트 머지 조인
동치식이 아닐때는 왜 성능이 떨어질까?
해시 조인은 해시 함수로 혜시 키를 만들다 보니 조인 키값이 정확하게 일치해야 효율적으로 동작합니다 만약 동치식이 아니라 등호로 조인 연산인 경우 비슷한 값이 많이 나와서 그것을 판별하는 것도 비용에 포함이 됩니다 그래서 대량 데이터에서 조인 조건이 동치식이 아닌 경우에는 소트 머지가 유리합니다 그럴 수밖에 없는 게 먼저 정렬을 하고 비교를 하다 보니 부등호나 LIKE BETWEEN 같은 경우도 비교를 잘하지만 정렬이 되어 있지 않은 해시 조인에서는 그 의미가 퇴색되게 됩니다
그럼에서 NL 조인
그래도 사실 NL 조인을 가장 먼저 고려 대상이 되어야 한다 NL 조인에 사용하는 인덱스는 DROP 하지 않는 이상 영구적으로 유지하면서 다양한 쿼리를 위해 공유 재사용하는 자료구조다 하지만 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 소멸하는 자료구조다 그렇기에 최우선적으로 NL 조인이 먼저 가능한지 고려하는 게 좋은 접근 방법이다