HASH Semi Join
1:N 조인 후 1쪽 컬럼만 DISTINCT 로 추리는 패턴은 HASH JOIN + HASH UNIQUE 두 단계로 분해됩니다. EXISTS + UNNEST + HASH_SJ 로 HASH JOIN SEMI 를 유도하면 두 단계가 하나로 합쳐져 PGA 사용량과 처리 행 수가 모두 감소합니다.
HASH JOIN 과 SEMI JOIN 의 장점을 합쳐, DISTINCT 처리를 위한 별도의 HASH UNIQUE 오퍼레이션까지 제거할 수 있습니다.
핵심 정리
- HASH JOIN 과 SEMI JOIN 의 장점을 합쳐 HASH UNIQUE 오퍼레이션도 제거할 수 있습니다.
- 1 : N 으로 JOIN 하는 테이블 중 1 쪽 테이블 컬럼 기준으로 결과를 보여줄 때 사용하면 효과적입니다.
원본 쿼리
DISTINCT + 일반 JOIN 패턴입니다. ORDERS (1) 와 ORDER_ITEMS (N) 가 1:N 관계인데, 결과 컬럼은 ORDERS 측만 사용합니다.
1
2
3
4
5
6
7
SELECT DISTINCT A.ORDER_DATE, A.EMPLOYEE_ID, A.ORDER_TOTAL ORDER_TOTAL
FROM ORDERS A, ORDER_ITEMS B
WHERE A.ORDER_ID = B.ORDER_ID
AND A.ORDER_DATE >= TO_DATE('20110601', 'YYYYMMDD')
AND A.ORDER_DATE < TO_DATE('20110901', 'YYYYMMDD')
AND B.ORDER_DATE >= TO_DATE('20110601', 'YYYYMMDD')
AND B.ORDER_DATE < TO_DATE('20110901', 'YYYYMMDD');
실행 계획
1
2
3
4
5
6
7
8
9
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 125K | 90441 | |
| 1 | HASH UNIQUE | | 1 | 125K | 90441 | 5491K (0) |
|* 2 | HASH JOIN | | 1 | 624K | 90441 | 7592K (0) |
|* 3 | TABLE ACCESS FULL | ORDERS | 1 | 125K | 19612 | |
|* 4 | TABLE ACCESS FULL | ORDER_ITEMS | 1 | 624K | 70829 | |
------------------------------------------------------------------------------------
HASH JOIN (Id 2) 이 624K 건의 결합 결과를 만들고, 그 위에서 HASH UNIQUE (Id 1) 가 125K 건으로 중복 제거합니다. 두 단계를 합쳐 약 13 MB (5491K + 7592K) 의 PGA 가 사용되었습니다.
수정 쿼리 — EXISTS + UNNEST HASH_SJ
ORDERS 측 컬럼만 결과로 사용하므로 ORDER_ITEMS 와의 관계는 “존재 여부 확인” 만 필요합니다. EXISTS 서브쿼리에 UNNEST (서브쿼리 평탄화) + HASH_SJ (해시 세미 조인 강제) 힌트를 주면 옵티마이저는 두 단계를 하나의 HASH JOIN SEMI 로 합칩니다.
1
2
3
4
5
6
7
8
9
SELECT DISTINCT A.ORDER_DATE, A.EMPLOYEE_ID, A.ORDER_TOTAL ORDER_TOTAL
FROM ORDERS A
WHERE A.ORDER_DATE >= TO_DATE('20110601', 'YYYYMMDD')
AND A.ORDER_DATE < TO_DATE('20110901', 'YYYYMMDD')
AND EXISTS (SELECT /*+ UNNEST HASH_SJ */ 1
FROM ORDER_ITEMS B
WHERE A.ORDER_ID = B.ORDER_ID
AND B.ORDER_DATE >= TO_DATE('20110601', 'YYYYMMDD')
AND B.ORDER_DATE < TO_DATE('20110901', 'YYYYMMDD'));
실행 계획
1
2
3
4
5
6
7
8
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 125K | 91682 | |
|* 1 | HASH JOIN SEMI | | 1 | 125K | 91682 | 7581K (0) |
|* 2 | TABLE ACCESS FULL | ORDERS | 1 | 125K | 19612 | |
|* 3 | TABLE ACCESS FULL | ORDER_ITEMS | 1 | 624K | 72070 | |
------------------------------------------------------------------------------------
HASH JOIN SEMI (Id 1) 한 단계로 끝납니다. ORDERS 의 각 행에 대해 ORDER_ITEMS 와의 매치가 한 건이라도 있으면 통과 — 더 이상 624K 의 중간 결과가 만들어지지 않고 곧장 125K 만 산출됩니다.
PGA 사용량은 약 7.6 MB 로 절반 수준입니다.
1 : N 조인의 결과를 1 쪽 컬럼만 DISTINCT 로 추릴 때는 일반 HASH JOIN + HASH UNIQUE 보다 HASH JOIN SEMI 가 한 수 위입니다.
EXISTS + UNNEST + HASH_SJ가 그 트리거 조합입니다.