-----------------------------------------------------------------------------------------------------------
- 공헌자 :
- darkbeom
-----------------------------------------------------------------------------------------------------------
2.3 해시조인
2.3.1 기본 메커니즘
- 7.3 버전에서 처음소개
- 소트머지 조인과 NL조인이 효과적이지 못한 상황에 대한 대안
1. 둘중 작은 집합(Build Input)을 읽어 Hash Area에 해시 테이블 생성한다.
(그림-1번 해시 함수에서 리턴 받은 버킷 주소로 찾아가 해시 체인에 엔트리를 연결)
2. 반대쪽 큰 집합(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인하는 방식이다.(그림-3번 해시 함수에서 리턴 받은 버킷 주소로 찾아가 해시 체인을 스캔하면서 데이터를 찾는다)
- 다른조인과의 차이점
1. NL조인 과 달리 Random 액세스 부하가 없다.(단, 양쪽집합을 읽는 과정에서 인덱스를 이용한다면 Random 액세스 발생)
2. NL조인 과 달리 Hash Area에 미리 생성해 둔 해시 테이블(또는 해시 맵)을 이용한다.
(해시테이블을 만드는 단계는 전체범위처리 불가피, Probe Input을 스캔하는 단계는 NL조인처럼 부분범위처리가능)
3. NL조인 과 달리 래치획득 과정없이 PGA에서 빠르게 데이터 탐색한다.
4. 소트머지조인과 달리 조인 전에 미리 양쪽 집합을 정렬하는 부담이 없다.
- 해시조인의 처리 메커니즘
--클러스터를 결정하기 위해 사용할 해시함수
create cluster h# ( bucket number ) hashkeys 16
hash is mod(bucket, 16);
create table dept_hashtable (bucket number, deptno number(2), dname varchar2(14) )
cluster h# (bucket);
insert into dept_hashtable
select mod(deptno, 16) bucket, deptno, dname from dept;
SET SERVEROUTPUT ON;
declare
l_bucket number;
begin
for outer in (select deptno, empno, rpad(ename, 10) ename from emp)
loop -- outer 루프
l_bucket := mod(outer.deptno, 16); -- 해시 함수를 적용해 클러스터(=버킷)확인
for inner in (select deptno, dname from dept_hashtable
where bucket = l_bucket -- 클러스터(=버킷)에서 탐색
and deptno = outer.deptno)
loop -- inner 루프
dbms_output.put_line(outer.empno||' : '||outer.ename||' : '||inner.dname);
end loop;
end loop;
end;
/
--처리결과
7369 : SMITH : RESEARCH
7499 : ALLEN : SALES
7521 : WARD : SALES
7566 : JONES : RESEARCH
7654 : MARTIN : SALES
7698 : BLAKE : SALES
7782 : CLARK : ACCOUNTING
7788 : SCOTT : RESEARCH
7839 : KING : ACCOUNTING
7844 : TURNER : SALES
7876 : ADAMS : RESEARCH
7900 : JAMES : SALES
7902 : FORD : RESEARCH
7934 : MILLER : ACCOUNTING
- 해시조인의 최적화
1. 해시 테이블을 생성하는 비용이 수반됨으로 Build Input이 작을때 효과적이다.
즉, PGA 메모리에 할당되는 Hash Area에 담길 정도로 충분히 작아야된다.
2. 해시키값으로 사용되는 컬럼에 중복값이 거의 없을때 효과적
2.3.2 힌트를 이용한 조인 순서 및 Build Input 조정
- 옵티마이져 활용 : use_hash 힌트.
- 사용자직접적 활용 : swap_join_inputs 힌트. 단,2개 테이블 해시조인시에는 ordered 나 leading 힌트를 사용해도 된다
SELECT /*+ USE_HASH(D E) */ D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 364 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
SELECT /*+ USE_HASH(D E) SWAP_JOIN_INPUTS(E) */ D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 364 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SELECT /*+ LEADING(E) USE_HASH(D) */ D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 364 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
2.3.3 두가지 해시 조인 알고리즘
- 그림 2-9
2.3.3.1 첫번째 알고리즘
SELECT /*+ LEADING(R, C, L, D, E)
USE_HASH(C) USE_HASH(L) USE_HASH(D) USE_HASH(E) */
E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME
, L.STREET_ADDRESS, L.CITY, C.COUNTRY_NAME, R.REGION_NAME
FROM REGIONS R, COUNTRIES C, LOCATIONS L, DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND L.LOCATION_ID = D.LOCATION_ID
AND C.COUNTRY_ID = L.COUNTRY_ID
AND R.REGION_ID = C.REGION_ID;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 10706 | 15 (14)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 10706 | 15 (14)| 00:00:01 |
|* 2 | HASH JOIN | | 27 | 2241 | 12 (17)| 00:00:01 |
|* 3 | HASH JOIN | | 23 | 1472 | 8 (13)| 00:00:01 |
|* 4 | HASH JOIN | | 25 | 700 | 5 (20)| 00:00:01 |
| 5 | TABLE ACCESS FULL| REGIONS | 4 | 56 | 3 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 25 | 350 | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | LOCATIONS | 23 | 828 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 513 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
2 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
3 - access("C"."COUNTRY_ID"="L"."COUNTRY_ID")
4 - access("R"."REGION_ID"="C"."REGION_ID")
- 전제
1. LEADING(R, C, L, D, E) : REGIONS > COUNTRIES > LOCATIONS > DEPARTMENTS > EMPLOYEES 순으로 조인하되
즉, ordered 나 leading 힌트는 조인 순서를 결정하기 위함이지 Build Input을 결정함이 아니다.
2. USE_HASH(C) USE_HASH(L) USE_HASH(D) USE_HASH(E) : 각각 해시조인으로 처리해라.
- 수행
1. id 4번 해시 조인 : REGIONS를 해시 테이블로 빌드하고(Build), COUNTRIES를 읽어 해시 테이블을 탐색하면서 조인수행
(Probe)
2. id 5번 해시 조인 : REGIONS와 COUNTRIES 조인결과를 해시 테이블로 빌드하고(Build), LOCATIONS를 읽어 해시 테이블
을 탐색하면서 조인 수행(Probe)
3. id 5번 해시 조인 : REGIONS와 COUNTRIES와 LOCATIONS 조인결과를 해시 테이블로 빌드하고(Build), DEPARTMENTS를
읽어 해시 테이블을 탐색하면서 조인 수행(Probe)
4. id 5번 해시 조인 : REGIONS와 COUNTRIES와 LOCATIONS와 DEPARTMENTS 조인결과를 해시 테이블로 빌드하고(Build),
EMPLOYEES를 읽어 해시 테이블을 탐색하면서 조인 수행(Probe)
- 결과 : ordered 나 leading 힌트의 역할은 조인 순서를 결정하는데 있지만, 처음 조인되는 두 집합간에(REGIONS와
COUNTRIES) Build Input을 정하는데에는 영향을 미친다.
2.3.3.2 두번째 알고리즘
SELECT /*+ LEADING(R, C, L, D, E)
USE_HASH(C) USE_HASH(L) USE_HASH(D) USE_HASH(E)
SWAP_JOIN_INPUTS(L)
SWAP_JOIN_INPUTS(D)
SWAP_JOIN_INPUTS(E)
*/
E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME
, L.STREET_ADDRESS, L.CITY, C.COUNTRY_NAME, R.REGION_NAME
FROM REGIONS R, COUNTRIES C, LOCATIONS L, DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND L.LOCATION_ID = D.LOCATION_ID
AND C.COUNTRY_ID = L.COUNTRY_ID
AND R.REGION_ID = C.REGION_ID;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 10706 | 15 (14)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 10706 | 15 (14)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 27 | 2241 | 12 (17)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 513 | 3 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 23 | 1472 | 8 (13)| 00:00:01 |
| 6 | TABLE ACCESS FULL | LOCATIONS | 23 | 828 | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 25 | 700 | 5 (20)| 00:00:01 |
| 8 | TABLE ACCESS FULL| REGIONS | 4 | 56 | 3 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 25 | 350 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
5 - access("C"."COUNTRY_ID"="L"."COUNTRY_ID")
7 - access("R"."REGION_ID"="C"."REGION_ID")
- 전제
1. 첫번째 알고리즘의 실행계획 ID 1~3번에 대한 Build Input을 사용자가 직접 조정하고자 할때(SWAP_JOIN_INPUTS 사용)
2. 네번의 조인과정을 거치지만 첫번째 알고리즘과 달리 NL조인처럼 순차적으로 진행하는것이 특징
- 수행
1. 해시테이블 생성 : COUNTRIES, LOCATIONS, DEPARTMENTS, EMPLOYEES에 대한 해시 테이블 생성
2. id 7번 해시조인 : COUNTRIES에서 한건을 읽어 REGIONS 해시 테이블을 탐색
3. id 5번 해시조인 : 2번에서 조인에 성공한 레코드는 LOCATIONS 해시 테이블을 탐색
4. id 3번 해시조인 : 3번에서 조인에 성공한 레코드는 DEPARTMENTS 해시 테이블을 탐색
5. id 1번 해시조인 : 4번에서 조인에 성공한 레코드는 EMPLOYEES 해시 테이블을 탐색
6. 2~5번 과정을 COUNTRIES 테이블(인덱스)을 모두 스캔할 때까지 반복한다.
- 결과
1. 가장 큰 EMPLOYEES 테이블을 해시 테이블로 생성 하였으므로 옵티마이저가 선택한 실행계획보다 비효율적
SELECT /*+ LEADING(D, E, L, C,R)
USE_HASH(E) USE_HASH(L) USE_HASH(C) USE_HASH(R)
SWAP_JOIN_INPUTS(L)
SWAP_JOIN_INPUTS(C)
SWAP_JOIN_INPUTS(R)
*/
E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME
, L.STREET_ADDRESS, L.CITY, C.COUNTRY_NAME, R.REGION_NAME
FROM REGIONS R, COUNTRIES C, LOCATIONS L, DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND L.LOCATION_ID = D.LOCATION_ID
AND C.COUNTRY_ID = L.COUNTRY_ID
AND R.REGION_ID = C.REGION_ID;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 10706 | 15 (14)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 10706 | 15 (14)| 00:00:01 |
| 2 | TABLE ACCESS FULL | REGIONS | 4 | 56 | 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 106 | 9222 | 12 (17)| 00:00:01 |
| 4 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 25 | 350 | 1 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 106 | 7738 | 10 (10)| 00:00:01 |
| 6 | TABLE ACCESS FULL | LOCATIONS | 23 | 828 | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 106 | 3922 | 7 (15)| 00:00:01 |
| 8 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 513 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("R"."REGION_ID"="C"."REGION_ID")
3 - access("C"."COUNTRY_ID"="L"."COUNTRY_ID")
5 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
7 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
-전제
1. DEPARTMENTS를 기준으로 EMPLOYEES > LOCATIONS > COUNTRIES > REGIONS 조인하되
2. EMPLOYEES를 Probe Input으로 삼고, 나머지는 모두 Build Input이 되도록 실행계획 조정
-수행
1. EMPLOYEES를 스캔하면서 DEPARTMENTS, LOCATIONS, COUNTRIES, REGIONS에 대한 해시테이블을
차례로 탐색하면서 조인 수행
-결과
1. EMPLOYEES가 최하위 자식 엔티티로서 가장 크고, 그외 모두 작은 코드성 테이블이다.
2. 따사서 위와간은 알고리즘으로 수행 된다면 작은 테이블로 빠르게 해시 테이블을 생성하고
3. 큰 테이블에서 일부 레코드만 스캔하다가 조인을 멈출수도 있다.
4. 즉, NL 조인의 가장 큰 장점인 부분범위처리를 해시 조인에서도 갖게 되는것이다.
반대로 해시 테이블로 빌드되는 작은 테이블들은 전체범위처리가 불가피하다.
2.3.4 Build Input이 Hash Area를 초과할 때 처리 방식
-전제 : In-Memory 해시 조인이 불가능할때
2.3.4.1 Grace 해시 조인(분할 Divide 및 정복 Conquer 방식)
- 파티션 단계
1. 조인되는 양쪽 집합 모두 조인 컬럼에 해시 함수 적용 반환된 해시 값에 따라 동적으로 파티셔닝을 실시한다.
2. 독립적으로 처리할수 있는 여러개의 작은 서브 집합으로 분할함으로써 파티션 짝을 생성하는 단계이다.
- 조인 단계
1. 파티션 단계가 완료 후 각 파티션 짝에 대해 하나씩 조인을 수행한다.
2. 파티션하기 전 어느쪽이 작은 테이블이었는지에 상관없이 각 파티션 짝별로 작은쪽이 Build Input으로선택해서 해시테이블을 생성한다. 해시테이블이 생성 되고나면 반대쪽 파티션 로우를 하나씩읽으면서 해시테이블을
탐색. 모든 파티션 짝에 대한 처리가 완료될때까지 반복한다.
3. 대부분 변형된 형태의 Hybrid 방식을 사용. 단 두개의 큰 테이블을 해시 조인하는 기본 알고리즘은Grace 해시조인에 바탕을 두고 있다.
4. 단점 : 디스크 I/O 부하
2.3.4.2 Hybrid 해시 조인
- 전제 : 디스크 I/O 부하 보안하고자
- 수행
1. 두테이블 중 작은쪽을 Build Input으로 선택하고 Hash Area에 해시테이블 생성하는데 이때 두개의 해시함수적용
- 첫번째 해시값으로는 레코드를 저장할 파티션(=버킷)을 결정
- 두번째 해시값으로는 나중(8단계)에 실제 조인할때를 위해 레코드와 함께 저장
2. 해시 테이블 생성 도중 Hash Area가 꽉 차면 그중 가장 큰 파티션(=버킷을 디스크에 기록
3. 해시테이블을 완성하기 위해 Build Input을 게속 읽는 동안 이미 디스크에 기록된 파티션에 해당되는 레코드는디스크 파티션에 기록
4. 다시 Hash Area가 꽉 차면 그중 가장 큰 파티션(=버킷)을 디스크에 기록
5. 첫번째 테이블에 대한 파티셔닝 단계가 끝나면 파티션 크기가 작은 순으로 메모리를 채운다.(가능한 많은 파티션을 담음으로써 6단계의 성공률를 높이기 위함)
6. 두번째 테이블을 읽기 시작. 첫번째 해시값에 해당하는 파티션이 현재 메모리에 있다면 비트-벡터 필더링에 의해그 파티션을 스캔하고, 거기서 조인 레코드를 찾으면 곧바로 결과집합에 포함하고 선택되지 않은 레코드는
그냥 버린다.
7. 비트-벡터 필더링은 통과 했지만 메모리에서 매칭되는 파티션을 찾지못하면 1번을 재수행.단 여기서는 비트-벡터 필더링을 거친 레코드만 디스크에 기록 된다.
8. 7번을 마치고 나면 양쪽 테이블 모두 같은 해시함수로써 파티셔닝했기 때문에 같은 해시값을 갖는 레코드끼리는같은 파티션 짝에 놓이기 되었다. 각 파티션 짝에 대해 하나씩 조인 수행 이때 작은쪽 파티션을 Build Input
으로 선택 후 1번과 7번단계에서 저장해 둔 두번째 해시 값을 이용해 해시 테이블을 생성한다.
9. 모든 파티션에 대해 8번과정 반복함으로써 해시조인종료.
2.3.4.3 Recursive 해시 조인(=Nasted-loop 해시조인, Multipass 해시조인)
- 원인 : 디스크에 기록된 파티션 짝끼리 조인 수행하려고 작은 파티션을 메모리 로드하는 과정에서 또다시 Hash Area를
초과하는 경우가 발생할 수 있는데 이때 추가적인 파티셔닝 단계를 거치는 과정을 말한다.
2.3.4.4 비트-벡터 필터링
- 전제 : 조인 성공 가능성이 없는 파티션 레코드는 아예 디스크에 기록되지 않게 하려는것.
1. f(m) = 'C', f(n) = 2 해시함수로 부터 리턴
2. 해시값 'C'에 해당하는 버킷에 그 레코드를 저장하면서
3. 비트-백터 2행 3열에 있는 비트도 1(positive)로 설정
4. Build Input에 대한 해시테이블을 생성하면서 파티셔닝 완료할때에 비트-백터를 참조함으로써 기록여부를 결정.
5. 반대로 비트-백터가 0(negative) 파티션에 조인되는 레코드가 없다는 확신으로 0일때에는 기록하지 않는다.
2.3.5 Build Input 해시 키 값에 중복이 많을때 발생하는 비효율
- 전제
1. 해시 충돌을 얼마나 최소화할수 있느냐? 이를 방지하려면 그만큼 많은 해시 버킷을 할당해야만한다.
2. 1버킷 : N키값 이 아닌 많은 개수의 버킷을 할당함으로써 대략적 1버킷 : 1키값 을 갖고자 함이다.
3. 그러나 해시 버킷을 아무리 많이 할당하더라도 해시 테이블에 저장할 키 컬럼에 중복 값이 많다면
하나의 버킷에 많은 엔트리가 달릴수 밖에 없다.
4. 결국 해시 버킷을 빨리 찾더라도 해시 버킷을 스캔하는단계에서 많은 시간을 허비 함으로써 탐색 속도가 현저히 저하
- 수행
1. 회원, 매도주문과 매수주문, 주문가격과 수량 업무 처리 데이터 모델.
2. 매수매도주문일자(주문체결 테이블)는 당일 주문은 당일에만 체결이 이뤄지므로 체결일자(주문체결 테이블)로 대체
SELECT /*+ use_hash(t o) index(t) index(o) */
o.상품번호, o.주문접수번호, o.회원번호
, o.매도매수구분, o.주문유형코드
, o.주문수량, o.주문유형코드
, t.체결가격, t.체결수량, (t.체결가격 * t.체결수량) 체결금액
FROM 주문체결 t, 주문 o
WHERE t.상품번호 = :상품번호
AND t.체결일자 = :체결일자
AND o.상품번호 = t.상품번호 -- 해시키(key)
AND o.체결일자 = t.체결일자 -- 해시키(key)
AND o.주문접수번호 IN (t.매도주문접수번호, t.매수주문접수번호)
-- 위와동일
AND o.주문접수번호 = decode(o.매도매수구분, '1', t.매도주문접수번호, t.매수주문접수번호)
3. 해시 키는 '=' 조인 컬럼만으로 결정됨으로 '상품번호' 와 '체결일자'이다.
그런데 두 값을 조건절로 입력 받았음으로 해시 버킷은 단 하나만 할당되고 체인으로 연결할 특정상품의 하루 체결건수는
평균적으로 수천건에 이른다.
4. 같은 상품번호와 체결일자에 레코드를 유일하게 식별하도록 만드는 주문접수번호가 해시키값으로 사용되지 못한 원인
--1안
SELECT /*+ use_hash(t o) index(o) */
o.상품번호, o.주문접수번호, o.회원번호
, o.매도매수구분, o.주문유형코드
, o.주문수량, o.주문유형코드
, t.체결가격, t.체결수량, (t.체결가격 * t.체결수량) 체결금액
FROM (
SELECT /*+ index(t) */
상품번호, 체결일자, 체결가격, 체결수량, 매도주문접수번호 AS 주문접수번호
FROM 주문체결 t
UNION ALL
SELECT /*+ index(t) */
상품번호, 체결일자, 체결가격, 체결수량, 매수주문접수번호 AS 주문접수번호
FROM 주문체결 t
) t, 주문 o
WHERE t.상품번호 = :상품번호
AND t.체결일자 = :체결일자
AND o.상품번호 = t.상품번호
AND o.체결일자 = t.체결일자
AND o.주문접수번호 = t.주문접수번호 -- 해시키값으로 사용
--2안(테이블을 두번 읽기 싫다면..)
CREATE TABLE 복제테이블 AS SELECT ROWNUM 번호 FROM dual CONNECT BY LEVEL <= 100;
SELECT /*+ use_hash(t o) index(o) */
o.상품번호, o.주문접수번호, o.회원번호
, o.매도매수구분, o.주문유형코드
, o.주문수량, o.주문유형코드
, t.체결가격, t.체결수량, (t.체결가격 * t.체결수량) 체결금액
FROM (
SELECT /*+ index(t) index(c) */
t.상품번호, t.체결일자, t.체결가격, t.체결수량,decode(c.번호, 1, t.매도주문접수번호, 2, t.매수주문접수번호) AS 주문접수번호
FROM 주문체결 t, 복제테이블 c
WHERE c.번호 <= 2
) t, 주문 o
WHERE t.상품번호 = :상품번호
AND t.체결일자 = :체결일자
AND o.상품번호 = t.상품번호
AND o.체결일자 = t.체결일자
AND o.주문접수번호 = t.주문접수번호 -- 해시키값으로 사용
- 결과
1. 주문접수번호가 해시키값으로 사용되도록 변경했을시에는 키중복이 많이 발생하지않기 때문에 더욱더 효율적으로
데이타를 스캔가능하다.
2.3.6 해시 조인 사용기준
- 기준
1. 한쪽 테이블이 Hash Area에 담길 정도로 충분히 작아야함.
2. Build Input 해시 키 컬럼에 중복 값이 거의 없어야 함.
- 조건
1. 조인 컬럼에 적당한 인덱스가 없어 NL조인이 비효율적일때
2. 조인 컬럼에 인덱스가 있더라고 NL 조인 드라이빙 집합에서 Inner 쪽 집합으로서의 조인 액세스량이 많아
Random 액세스 부하가 심할때
3. 소트 머지 조인하기에는 두 테이블이 너무 커 소트 부하가 심할때
4. 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할때
- 결과
1. 해시테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조이다.
2. 수행빈도가 낮고
3. 쿼리수행 시간이 오래걸리는
3. 대용량테이블을 조인할때(배치, DW, OLAP성 쿼리)
'ORACLE > 튜닝' 카테고리의 다른 글
| SQL Profile 이용 SQL Plan 변경 (이전 글 PLAN_SET_OUTLINE 과 같이 알아두기) (0) | 2017.08.31 |
|---|---|
| PLAN_SET_OUTLINE 이용한 SQL Plan 변경 (0) | 2017.08.30 |
| use_hash_aggregation - GROUP and ORDER BY (0) | 2017.08.18 |
| HINT로 날개를 달자 (0) | 2017.08.04 |
| [Hint] SWAP_JOIN_INPUTS (0) | 2017.07.19 |