#35_140801_TUNING_JOIN TUNING2

# JOIN TUNING

l HASH JOIN RIGHT SEMI

- hash join을 하는데 오른쪽에 있는 쿼리부터 조인시키겠다는 의미이다

문제. 아래의 실행계획을 hash join right semi로 만드시오

SELECT /*+ qb_name(main) */ ename

FROM EMP

WHERE empno IN (SELECT /*+ qb_name(sub) hash_sj swap_join_inputs(emp@sub) */ mgr FROM emp);

- hash join은 main과 sub의 순서를 바꾸기 위해서는 swap_join_inputs 문장을 써줘야 한다

- 그래서 괄호안에 테이블 이름을 쓰면 되지만 위 같은 경우에는 괄호안에 emp@ 이후에 query block name의 이름을 써주면 된다

- 만약에 서브쿼리와 메인쿼리의 테이블이 다르다면 테이블이름만 써주면 된다

n 실제실행계획

---------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time    ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |      |      1 |        |       |     7 (100)|          |      0  |*  1 |  HASH JOIN RIGHT SEMI|      |      1 |      6 |    66 |     7  (15)| 00:00:01 |      0  |*  2 |   TABLE ACCESS FULL  | EMP  |      1 |     13 |    39 |     3   (0)| 00:00:01 |      0  |   3 |   TABLE ACCESS FULL  | EMP  |      0 |     14 |   112 |     3   (0)| 00:00:01 |       ------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$9E23E2A3    2 - SEL$9E23E2A3 / EMP@SSUB    3 - SEL$9E23E2A3 / EMP@MAIN

l HASH JOIN ANTI

l anti join

- 서브쿼리와 메인쿼리 사이의 연산자가 not in 경우

1.     nested loop anti join : /*+ nl_aj */

2.     sort merge anti join : /*+ merge_aj */

3.     hash anti join : /*+ hash_aj */

문제67. 관리자가 아닌 사원들의 이름을 출력하시오(자기밑에 직속부하가 없는 사원들)

SELECT /*+ qb_name(main) */ ename

FROM EMP

WHERE empno NOT IN

 (SELECT /*+ qb_name(sub) hash_sj swap_join_inputs(emp@sub) */ mgr FROM EMP

WHERE mgr IS NOT null);

- anti join 도 semi join 처럼 메인쿼리 테이블을 먼저하고 그 다음에 서브쿼리를 수행한다

문제68. 관리자가 아닌 사원들의 이름을 출력하시오. 실행계획이 서브쿼리의 테이블부터 드라이빙 되도록 하고 실행계획이 hash right anti join 이 되도록 하시오

SELECT /*+ qb_name(main) */ ename FROM EMP WHERE empno NOT IN  (SELECT /*+ qb_name(sub) hash_aj swap_join_inputs(emp@sub) */ mgr FROM EMP WHERE mgr IS NOT null); - SNA는 single null available 이란 뜻으로 null 값을 받아들이겠다는 뜻이다 - 현재 emp 테이블의 empno 컬럼에 not null 제약이 걸려있는데 이 제약을 없애면 hash join right semi 가 되지 않는다 SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name='EMP'; ALTER TABLE EMP DROP CONSTRAINTS SYS_C0011262; - hash right anti join 은 empno가 not null 이 보장 되야 가능하다

SELECT /*+ qb_name(main) */ ename FROM EMP WHERE empno NOT IN  (SELECT /*+ qb_name(sub) hash_aj swap_join_inputs(emp@sub) */ mgr FROM EMP   WHERE mgr IS NOT null) AND empno IS NOT NULL; - 서브쿼리를 사용할 때 not in을 사용할 때 위 처럼 not null 조건이 있어야 성능이 좋아진다 - 시스템은 null 값이 있는지 없는지 잘 모르기 때문에 not null 조건이 필요하다

문제68. 아래의 SQL을 서브쿼리로 수행했을 때와 hash semi join 으로 수행했을 때 차이를 비교해주시오

# 튜닝 전

select count(*) from sales100 where prod_id in (select prod_id from products100 where prod_name LIKE 'Deluxe%'); - 7초 수행

# 튜닝 후

# 순수하게 서브쿼리만 수행할 때 select count(*) from sales100 where prod_id in (select /*+ no_unnest push_subq */ prod_id from products100 where prod_name LIKE 'Deluxe%'); - 0초 수행 # Hash semi join 으로 수행 select count(*) from SALES100 s where prod_id in (select /*+ hash_sj no_swap_join_inputs(p) */    prod_id from products100 p where prod_name LIKE 'Deluxe%'); - 8초 수행 # hash right semi join 으로 수행 select count(*) from SALES100 s where prod_id in (select /*+ hash_sj swap_join_inputs(p) */    prod_id from products100 p where prod_name LIKE 'Deluxe%'); - 7초 수행