2.29 SJR*,AJR*(Hash Jin Right Semi/Anti) :

Hash Semi /Anti Join 시 사용되는 서브쿼리 집합을

Build Input 집합으로 변환하라.

 

Hash Join Right(Semi/Anti/Outer)

>Oracle 11g 기능 추가.

> 대용량 집합의 Join 시에 획기적인 기능 향상

> Hash Join 에 Right 옵션이 붙음으로써 획기적인 성능 향상을 이룸.

 

-- 기능 향상의 이유는 무엇일까.?---

SQL/Anti Join

>메인쿼리 수행 후 > 서브쿼리 체크

>서브쿼리 는 항상 후행집합

>Hash Outer Join 으 ㅣ경우도 마찬가지로 (+) 표시가 붙는 쪽의 집합은 항상 후행 집합

 

그러나!

10g 부터 Hash Join Right( Semi/Anti/Outer) 기능이나오면서 서브쿼리 / 아우터 Join 되는 쪽의 집합이선행 집합이 될 수있다.

9i 까지 Hash Join ( Semi Join/Anti/Outer ) 의 경우 눈물을;; 머금고 대량의 집합을 선행 처리.

그러나 이제는 SJR 을 사용하여 자유롭게 선행 집합을 선택 할수 있는 것이다.

초 대용량 DB 에 이 기능은 정말정말 중요하다!

 

예 )

 

** 테이블을 생성하고 통계정보를 생성한다.

CREATE TABLE big_emp AS
SELECT ROWNUM AS employee_id,first_name,last_name,email,phone_number,
       hire_date,job_id,salary,commission_pct,manager_id,department_id,emp_kind
FROM employee a,
(SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) b;

 

ALTER TABLE big_emp
    ADD (CONSTRAINT pk_big_emp PRIMARY KEY ( employee_id) USING INDEX);
   

exec dbms_stats.gather_table_stats(user,'BIG_EMP',cascade =>true );

 

2.Hash Semi Join 을 유도한다.

 

SELECT /*+GATHER_PLAN_STATISTICS*/
    a.employee_id,a.salary
FROM big_emp a
WHERE EXISTS ( SELECT /*+ USE_HASH(b)*/
                1
               FROM department b
               WHERE b.department_id = a.department_id);

 

 

------------------------------------------+-----------------------------------+
| Id  | Operation             | Name      | Rows  | Bytes | Cost  | Time      |
------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT      |           |       |       |   862 |           |
| 1   |  HASH JOIN RIGHT SEMI |           |  207K | 3312K |   862 |  00:00:06 |
| 2   |   INDEX FULL SCAN     | DEPT_ID_PK|    27 |   108 |     1 |  00:00:01 |
| 3   |   TABLE ACCESS FULL   | BIG_EMP   |  209K | 2508K |   858 |  00:00:06 |
------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("B"."DEPARTMENT_ID"="A"."DEPARTMENT_ID")

 

작은 용량 테이블 DEPT를 Driving 집합 ( build Input ) 으로 선택

BIG_EMP 테이블을 후행으로 (Probe) 테이블로 Hash Join

=> 최적화 조인 탄생!

 

그럼.

Hash Join Right Semi를 사용하지 않는 다면 ..?

=> Subquery Unnesting 기능을 이용 작은용량 테이블 DEPT를 Driving 집합 ( Build Input )으로 선택

=> 하지만 아래 SQL 과 같은 손해를 감수 해야 한다.

 

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

 

SELECT /*ORDERED*/
    a.employee_id,a.salary
FROM big_emp a
WHERE EXISTS ( SELECT /*+USE_HASH(b)*/
                1
               FROM department b
               WHERE b.department_id = a.department_id );

ALTER SESSION SET EVENTS '10053 trace name context off';

 

> 강제로 MAIN 쿼리에 ORDERED 힌트  : Semi Join 에서  Subquery Unnesting 으로..

> ORDERED 힌트 준 이유 : 서브쿼리가  Semi Join 에 실패할 경우 Subquery Unnesting 을 시도하게 되는데 이때 서브쿼리 블럭이 From 절의 가낭 좌측으로 오기 때문.

> 사용자가 ORDERED 힌트를 사용하면 오라클 내부적인 leading 힌트와 SQAP_JOIN_INPUT 힌트등이 Override 되어 무시된다.

==>> 고로  Semi Join 이 아닌 Subquery Unnesting  이 적용

위의 사실이 ORDERED 힌트를 주의 해서 사용해야 하는 이유가 된다.

 

----------------------------------------+-----------------------------------+
| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |           |       |       |   863 |           |
| 1   |  HASH JOIN          |           |  207K | 3313K |   863 |  00:00:06 |
| 2   |   SORT UNIQUE       |           |    27 |   108 |     1 |  00:00:01 |
| 3   |    INDEX FULL SCAN  | DEPT_ID_PK|    27 |   108 |     1 |  00:00:01 |
| 4   |   TABLE ACCESS FULL | BIG_EMP   |  209K | 2508K |   858 |  00:00:06 |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("B"."DEPARTMENT_ID"="A"."DEPARTMENT_ID")

 

 

Hash Semi Join  vs Subquery unnesting

1.조인순서 Scan 블럭수 Hash Area 사용량 대동 소이

2.SUbquery Unnesting  : 불필요한 SortUnique Operation 발생

이유 : SUbquery Unnesting 은 메인 쿼리의 결과 집합을 보존하기 위하여 Sort Unique 혹은 Hash Unique 작업이 추가적으로 발생한다.

 

*SUbquery Unnesting 가 항상 나쁜것은 아니지만 대용량 집합간의 조인 시는 엄청난 부담이 된다.

이유는 Sort Unique 작업이 추가적으로 필요 하기 떄문이다.하지만 덩치가 큰 BIG_EMP를 선행 테이블로 할수도없는 것이다. 그 이유로 HAsh Join Right Semi 가 10g 에 나타났다.

 

그렇다면 이 경우 Hash Join Right 어떻게 발생 시킬까..?

> QB_NAME 을 이용한 Global 힌트 + USE_HASH + SWAP_JOIN_INPUTS

 

SELECT /*+LEADING(a) USE_HASH(@sub b) SWAP_JOIN_INPUTS(@sub b )*/
    a.employee_id,a.salary
FROM big_emp a
WHERE EXISTS ( SELECT /*+QB_NAME(sub)*/
                1
               FROM department b
               WHERE b.department_id = a.department_id );

ALTER SESSION SET EVENTS '10053 trace name context off';

 

============
Plan Table
============
------------------------------------------+-----------------------------------+
| Id  | Operation             | Name      | Rows  | Bytes | Cost  | Time      |
------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT      |           |       |       |   862 |           |
| 1   |  HASH JOIN RIGHT SEMI |           |  207K | 3312K |   862 |  00:00:06 |
| 2   |   INDEX FULL SCAN     | DEPT_ID_PK|    27 |   108 |     1 |  00:00:01 |
| 3   |   TABLE ACCESS FULL   | BIG_EMP   |  209K | 2508K |   858 |  00:00:06 |
------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("B"."DEPARTMENT_ID"="A"."DEPARTMENT_ID")

 

 

위와 같은 힌트를 준 결과 다시 HASH JOIN RIGHT SEMI PLAN 으로 돌아왔다.

힌트 설명

QB_NAME : 쿼리블럭명 지정

SWAP_JOIN_INPUTS : PROBE 쪽 집합(후행 집합)을 제로 Build Input 집합 ( 선행 집합) 으로변경

 

 

HASH JOIN RIGHT ANTI PLAN 으로 유도하는 예제

> Exists 대신 Not Exists 로 변경

 

SELECT /*+LEADING(a) USE_HASH(@sub b) SWAP_JOIN_INPUTS(@sub b )*/
    a.employee_id,a.salary
FROM big_emp a
WHERE NOT EXISTS ( SELECT /*+QB_NAME(sub)*/
                1
               FROM department b
               WHERE b.department_id = a.department_id );

ALTER SESSION SET EVENTS '10053 trace name context off';

 

============
Plan Table
============
------------------------------------------+-----------------------------------+
| Id  | Operation             | Name      | Rows  | Bytes | Cost  | Time      |
------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT      |           |       |       |   862 |           |
| 1   |  HASH JOIN RIGHT ANTI |           |  2140 |   33K |   862 |  00:00:06 |
| 2   |   INDEX FULL SCAN     | DEPT_ID_PK|    27 |   108 |     1 |  00:00:01 |
| 3   |   TABLE ACCESS FULL   | BIG_EMP   |  209K | 2508K |   858 |  00:00:06 |
------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("B"."DEPARTMENT_ID"="A"."DEPARTMENT_ID")

 

HASH JOIN RIGHT 가 가능합을 알수 있다.

 

 

HASH OUTER RIGHT JOIN 예제

 

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

 

SELECT /*+LEADING(a) USE_HASH(@sub b) SWAP_JOIN_INPUTS(@sub b )*/
    a.employee_id,a.salary,b.department_name
FROM big_emp a,department b
WHERE a.department_id = b.department_id(+);

ALTER SESSION SET EVENTS '10053 trace name context off';

 

 

============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id  | Operation              | Name      | Rows  | Bytes | Cost  | Time      |
-------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |           |       |       |   865 |           |
| 1   |  HASH JOIN RIGHT OUTER |           |  209K | 5852K |   865 |  00:00:07 |
| 2   |   TABLE ACCESS FULL    | DEPARTMENT|    27 |   432 |     4 |  00:00:01 |
| 3   |   TABLE ACCESS FULL    | BIG_EMP   |  209K | 2508K |   858 |  00:00:06 |
-------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")

 

HASH OUTER JOIN 에서도 HASH JOIN RIGHT 가 성공적으로실행.

 

이로써

HASHJOIN RIGHT(SEMI/ANTI/OUTER) 개념과 용도 설명이 모드 끝났다.

 

CONTROL 파라미터

_right_outer_hash_enable = true (defualt)

BUT!  

SWAP_JOIN_INPUTS /NO_SQAP_JOIN_INPUTS 힌트를 이용 하여 해당 JOIN 에만 사용하는 것이 바람직.!

 

*** ORACLE 10g 부터나온 Hash Join Right ( Semi/Anti/Outer )  기능을 적재 적소에 활용 한다면

대용량 집합간의 Join  성능을 획기적으로 향상이 가능하다.!!!! ***

 

참고사항 :

Hash Join Right(Semi/Anti)가 Query Transformation 이 아니라는 의견이 있었다.

하지만  Hash Join Right(Semi/Anti) 는   Query Transformation 의 기능 중에서 Semi Join 혹은 Anti Join의 한종류이므로 반박의 여지가 없다.

이것은 DSJ( Driving Semi Join ) 도 마찬가지 이다.

하지만 Hash Join Right Outer 기능은  Query Transformation 과 상관 없다.

이것은 SQL이 변경된 것이 아니라 조인의 순서만 바뀐것 이기 떄문이다.