<2009.03.11 : 아래 내용중의 Right 의 의미는 Oracle 10g Performance Tuning Guide 19-21 에 나온대로 "올바른" 이란 뜻이 아니라 "Left 혹은 Right Outer Join 시의 Right" 의 의미로 바꿉니다.
하지만 이글의 핵심인 "이전버젼까지는 항상 후행집합으로 되던것이 10g 부터는 선행집합이 될수 있다." 는 바뀐것이 없습니다. > 오늘은 Hash Join Right (Semi/Anti/Outer) 의 용도에 대하여 알아보려한다.
Oracle 10g 부터 Hash Join 은 서서히 변화하기 시작하였다.
특히 Hash Join Right (Semi/Anti/Outer) 기능을 사용하여 대용량 집합의 Join 시 획기적인 성능향상을 이루었다.
Hash Join 에서 Right 옵션이 붙음으로서 획기적인 성능향상을 이루었다는 이유는 무엇일까?
Semi/Anti Join은 항상 메인 쿼리가 수행된 후 서브쿼리의 데이터를 체크하는 방식이다.
따라서
Semi/Anti Join 의 경우 서브쿼리는 항상 후행집합이 될수 밖에 없다.
Hash Outer Join 의 경우도 마찬가지로 (+) 표시가 붙는 쪽의 집합은 항상 후행집합이 될수 밖에 없었다.하지만 10g 부터 Hash Join Right (Semi/Anti/Outer) 기능이 나오게 되면서 서브쿼리 혹은 아우터 Join 되는 쪽의 집합이 선행집합이 될수 있다.
이때 Right 의 뜻은 left 집합 대신에 right(후행집합)을 선행집합으로 하겠다는 뜻이다.
9i 까지 Hash Join (Semi/Anti/Outer)의 경우에 눈물을 머금고 대량의 집합을 선행처리할수 밖에 없었지만 이제는 자유롭게 선행집합을 선택할수 있는것이다.
국내외 튜닝책을 막론하고 이막강한 기능에 대하여 제대로 다루는 것을 본적이 없다.
왜냐하면 초대용량 DB(VLDB)에서 Join Method 를 튜닝해본 사람만이 이 기능이 얼마나 중요한지 피부로 느낄수 있기 때문이다.
아래의 스크립트를 보자.
환경 : 10.2.0.4
1.테이블을 생성하고 Aanlyze 를 한다.
1.테이블 생성
CREATE TABLE BIG_EMP AS
SELECT ROWNUM AS EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO
FROM EMP A,
(SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;
ALTER TABLE BIG_EMP
ADD ( CONSTRAINT PK_BIG_EMP PRIMARY KEY (EMPNO) USING INDEX );
dbms_stats.gather_table_stats(user, 'BIG_EMP', cascade => true);
2.Hash Semi Join 을 유도한다.
select a.empno, a.sal
from big_emp a
where exists (select /*+ use_hash(b) */
b.deptno
from dept b
where b.deptno = a.deptno
) ;
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts |Cost (%CPU)| A-Rows | A-Time | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN RIGHT SEMI| | 1 | 4 (25)| 26000 |00:00:00.06 | 176 | 813K (0)|
| 2 | INDEX FULL SCAN | PK_DEPT | 1 | 1 (0)| 4 |00:00:00.01 | 1 | |
|* 3 | TABLE ACCESS FULL | BIG_EMP | 1 | 2 (0)| 26000 |00:00:00.01 | 175 | |
--------------------------------------------------------------------------------------------------------
위의 통계정보를 보면 176 블럭을 scan 했으며 Hash area size 를 813 K를 사용했다는걸 알수 있다.
작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택하고 BIG_EMP 테이블을
후행(Probe) 테이블로 Hash 조인 함으로서 최적의 조인이 되었다.
그렇다면 Hash Join Right Semi 를 사용하지 않으면 어떻게 될것인가?
Subquery Unnesting 기능을 이용하면 작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택할수는 있다.
하지만 아래처럼 약간의 손해를 감수해야 한다.
select /*+ gather_plan_statistics ordered */ a.empno, a.sal
from big_emp a
where exists (select /*+ use_hash(b) */
b.deptno
from dept b
where b.deptno = a.deptno
);
위의 SQL 을 보면 강제로 MAIN 쿼리에 ordered 힌트를 주어 Semi Join 이 아닌 SubQuery Unnesting 이 되었다.
ordered 힌트를 사용한 이유는 서브쿼리가 Semi Join 에 실패할 경우 Subquery Unnesting 을 시도하게 되는데
이때 서브쿼리블럭이 From 절의 가장 좌측으로 오기 때문이다.
사용자가 ordered 힌트등을 사용하면 오라클 내부적인 leading 힌트와 Swap_join_inputs 힌트 등이 Override 되어 무시된다.
따라서 Semi Join 이 아닌 Subquery Unnesting 되는 것이다.
이제 Plan 을 보자.
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts |Cost (%CPU)| A-Rows | A-Time | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 5 (40)| 26000 |00:00:00.37 | 176 | 808K (0)|
| 2 | SORT UNIQUE | | 1 | 1 (0)| 4 |00:00:00.01 | 1 | 2048 (0)|
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 1 (0)| 4 |00:00:00.01 | 1 | |
|* 4 | TABLE ACCESS FULL| BIG_EMP | 1 | 2 (0)| 26000 |00:00:00.31 | 175 | |
------------------------------------------------------------------------------------------------------ 처음 예제와 조인순서와 Scan 한 블럭수및 Hash area size 사용량은 대동소이 하지만 Subquery Unnesting 이 발생하여 불필요한 Sort 가 발생 하였다.
위의 SQL 의 경우 Subquery Unnesting 은 메인쿼리의 결과집합을 보존하기 위하여 Sort Unique 혹은 Hash Unique 작업이 추가적으로 발생된다.
Subquery Unnesting 이 항상 나쁜것은 아니지만 대용량 집합간의 조인시는 엄청난 부담이 될수 밖에 없다.
서브쿼리쪽은 Sort Unique 작업이 추가적으로 필요하기 때문이다.
그렇다고 덩치가 큰 BIG_EMP를 선행테이블로 할수도 없는것이다.
이것이 바로 Hash Join Right Semi 가 10g 에서 나타난 이유이다.
그렇다면 이럴 경우에 강제로 Hash Join Right Semi 를 발생시키려면 어떻게 해야 되겠는가?
이럴때 간단하게 사용할수 있는것이 QB_NAME 을 이용한 Global 힌트와 USE_HASH 및 SWAP_JOIN_INPUT 힌트이다.
아래의 스크립트를 보자.
select /*+ gather_plan_statistics LEADING(A) USE_HASH(@sub B) SWAP_JOIN_INPUTS(@sub B) */ a.empno, a.sal
from big_emp a
where exists (select /*+ qb_name(sub) */
b.deptno
from dept b
where b.deptno = a.deptno
);
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN RIGHT SEMI| | 1 | 4 (25)| 26000 |00:00:01.05 | 176 | 813K (0)|
| 2 | INDEX FULL SCAN | PK_DEPT | 1 | 1 (0)| 4 |00:00:00.01 | 1 | |
|* 3 | TABLE ACCESS FULL | BIG_EMP | 1 | 2 (0)| 26000 |00:00:00.99 | 175 | |
---------------------------------------------------------------------------------------------------------다시 정상적인 Hash Join Right Semi 로 돌아왔다.
간단히 힌트를 설명하자면 QB_NAME 은 쿼리블럭명을 강제로 지정하는 힌트이고 Swap_join_inputs 힌트는
Probe 쪽 집합(후행 집합) 을 강제로 Build Input 집합(선행집합) 으로 바꾸는 힌트이다.
그리고 Use_hash 힌트에 대하여 한마디 하자면 원래 Use_hash 힌트는 후행 집합에 대해서만 사용하는 힌트이다.
하지만 USE_HASH(A B) 이런식으로 사용해도 ORACLE 이 힌트를 아래처럼 변환시켜버린다.
USE_HASH(A B) --> LEADING(A B) USE_HASH(B)
오라클사에서 명시적인 용어가 없기 때문에 필자는 이것을 Internal Hint Transformation 이라 부른다.
다음에 기회가 되면 Internal Hint Transformation 에 대하여 글을 올릴까 한다.
결론 :
10g 부터 나온 Hash Join Right (Semi/Anti/Outer) 기능을 적재적소에 활용하면 대용량 집합간의 join 성능을 획기적으로 향상시킬수 있다. 참고로 Hash Join Right Anti Plan 으로 유도하는 것은 Exists 대신 Not Exists 로 바꾸면 된다.
Hash Join Right Outer 를 유도하는 예제는 아래와 같다.
select /*+ LEADING(A) USE_HASH(B) SWAP_JOIN_INPUTS(B) */ a.empno, a.sal
from big_emp a,
dept b
where a.deptno = b.deptno(+)
invalid-file
