2. SQL Hint (힌트)

-- ############################################################################
-- SQL Hint (힌트)
--
-- 1. 실습 스키마 생성
-- 2. 힌트가 무시되는 경우
-- 3. 자주 사용하는 힌트
-- ############################################################################
 
-------------------------------------------------------------------------------
-- 1. 실습 스키마 생성 scott
-------------------------------------------------------------------------------
$sqlplus "/ as sysdba"
sql> @?/rdbms/admin/scott.sql
sql> alter user scott identified by tiger;
 
 
-------------------------------------------------------------------------------
-- 2. Hint가 무시되는 경우
-------------------------------------------------------------------------------
create index emp_x1 on emp(deptno);
 
-- ▶ 논리적으로 불가능한 액세스 경로 
 
select /*+ index(e (deptno)) */ count(* )
  from emp e 
 order by deptno
;
-- 단일 컬럼 인덱스일 때 값이 null 이면 인덱스 레코드에 포함되지 않는다.
-- deptno는 null 허용 컬럼, index를 이용해 count 한다면 결과가 맞지 않으므로 무시
 
select /*+ index(e (deptno)) */ * 
  from emp e 
 where deptno is not null 
 order by deptno nulls first
;
-- not null 컬럼이 아닌 경우, 인덱스를 이용해 정렬을 대체 할 수 없다.
-- sort 연산 제거되지 않음
 
select /*+ leading(e) use_merge(d) */ *
  from dept d, emp e
 where e.deptno (+) = d.deptno
;
-- outer join 조인 순서에 의해 emp 가 먼저 driving 될 수 없으므로 무시
-- (+) 가 붙지 않는 테이블이 기준 테이블이며 먼저 Driving 된다.
 
select /*+ leading(a) use_hash(b) */ a.empno 사원번호
     , min(a.sal) 급여
     , sum(b.sal) 누적급여
  from emp a, emp b
 where a.empno >= b.empno
 group by a.empno
 order by a.empno
;
-- 조인 조건이 등치(=) 조건인 경우만 hash join 으로 풀릴수 있기 때문에 무시.
 
--▶ 의미적으로 맞지 않게 기술된 힌트
 
select /*+ leading(d) use_nl(e) merge(e) push_pred(e) */ e.deptno, d.dname, e.cnt, e.avg_sal
  from dept d, (select deptno, count(*) cnt, avg(sal) avg_sal 
                  from emp 
                 group by deptno) e
 where d.deptno = e.deptno
;
-- 조인 조건 push down : 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣은 것
-- view merging 발생하면 쿼리 블록이 병합되므로, push_pred 수행 불가 함
-- push_pred 사용시 no_merge 힌트와 함께 사용
 
select /*+ leading(d) use_nl(e) no_merge(e) push_pred(e) */ e.deptno, d.dname, e.cnt, e.avg_sal
  from dept d, (select deptno, count(*) cnt, avg(sal) avg_sal 
                  from emp 
                 group by deptno) e
 where d.deptno = e.deptno
;
 
select deptno, dname, loc
  from dept d
 where exists ( select /*+ unnest push_subq(e) */ 'x' 
                  from emp 
                 where deptno = d.deptno)
;
-- pushing 서브쿼리 : 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제 하는것(unnest 되면 join으로 풀리게됨)
-- push_subq 는 unnesting 되지 않은 서브쿼리에서만 작동한다.
-- push_subq 힌트와 unnest 힌트가 의미적으로 맞지 않음
 
select deptno, dname, loc
  from dept d
 where exists ( select /*+ no_unnest push_subq(e) */ 'x' 
                  from emp e 
                 where deptno = d.deptno)
;
 
select deptno, dname, loc
  from dept d
 where exists ( select /*+ no_unnest nl_sj */ 'x' 
                  from emp 
                 where deptno = d.deptno)
;
-- nl_sj join으로 풀리기 위해서는 서브쿼리가 unnesting 되어 join으로 풀려야 가능하므로 no_unnest 힌트에 의해서 무시됨
-- no_unnest 힌트와 nl_sj 가 의미적으로 맞지 않음
 
select deptno, dname, loc
  from dept d
 where exists ( select /*+ unnest nl_sj */ 'x' 
                  from emp 
                 where deptno = d.deptno)
;
 
--▶ 옵티마이저에 의해 내부적으로 쿼리가 변환된 경우
 
select /*+ leading(a) use_nl(b) */ a.empno, b.ename, b.deptno, b.dname, b.sal
  from emp a, ( select /*+ no_merge */ e.empno, e.ename, e.sal, d.deptno, d.dname
                  from emp e, dept d
                 where d.deptno = e.deptno ) b
 where a.empno = b.empno
;
--view merging 으로 인해 내부적으로 쿼리가 변환되어 힌트가 먹지 않음!!
 
 
-------------------------------------------------------------------------------
-- 3. 자주 사용하는 힌트
-------------------------------------------------------------------------------
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- FULL
-- ▲▲▲▲▲▲▲▲▲▲▲
-- table full scan 유도
select /*+ full(e) */ count(*) from emp e;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- INDEX
-- ▲▲▲▲▲▲▲▲▲▲▲
-- index Scan 유도
select /*+ index(e (empno)) */ count(*) from emp e;
select /*+ index(e pk_emp) */ * from emp e;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- INDEX_DESC
-- ▲▲▲▲▲▲▲▲▲▲▲
-- index를 역순으로 sacn 유도
select /*+ index_desc(e (empno)) */ count(*) from emp e;
select /*+ index_desc(e pk_emp) */ count(*) from emp e;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- INDEX_FFS
-- ▲▲▲▲▲▲▲▲▲▲▲
-- index fast full scan 유도
select /*+ index_ffs(e (empno)) */ count(*) from emp e;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- INDEX_SS
-- ▲▲▲▲▲▲▲▲▲▲▲
-- index skip scan 유도
create index emp_x2 on emp(comm,deptno);
select /*+ index_ss(e emp_x2) */ * from emp e where comm >= 300;
drop index emp_x2;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- ORDERED
-- ▲▲▲▲▲▲▲▲▲▲▲
-- from 절에 나열된 순서대로 조인
select /*+ ordered */ * from emp e, dept d where e.deptno = d.deptno;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- LEADING
-- ▲▲▲▲▲▲▲▲▲▲▲
-- leading 힌트 괄호에 기술한 순서대로 조인
select /*+ leading(d e) */ * from emp e, dept d where e.deptno = d.deptno;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- SWAP_JOIN_INPUTS
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 해시 조인 시, BUILD_INPUT을 명시적으로 선택
select /*+ ordered use_hash(d) */ * 
  from emp e, dept d 
 where e.deptno = d.deptno
   and e.deptno = 10
;
--> ordered 힌트에 의해 build_input -> emp
 
select /*+ swap_join_inputs(d) use_hash(e) */ * 
  from emp e, dept d 
 where e.deptno = d.deptno
   and e.deptno = 10
;
--> build_input -> dept
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- USE_NL
-- ▲▲▲▲▲▲▲▲▲▲▲
-- nl 조인으로 유도
select /*+ leading(d) use_nl(e) */ * 
  from emp e, dept d 
 where e.deptno = d.deptno
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- USE_MERGE
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 소트 머지 조인으로 유도
select /*+ use_merge(e) */ * 
  from emp e, dept d 
 where e.deptno = d.deptno
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- USE_HASH
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 해시 조인으로 유도
select /*+ use_hash(e) */ * 
  from emp e, dept d 
 where e.deptno = d.deptno
   and e.deptno = 10
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- NL_SJ
-- ▲▲▲▲▲▲▲▲▲▲▲
-- NL 세미조인 유도
select deptno, dname, loc
  from dept d
 where exists ( select /*+ unnest nl_sj */ 'x' 
                  from emp 
                 where deptno = d.deptno)
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- MERGE_SJ
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 소트 머지 세미조인 유도
select deptno, dname, loc
  from dept d
 where exists ( select /*+ unnest merge_sj */ 'x' 
                  from emp 
                 where deptno = d.deptno)
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- HASH_SJ
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 해시 세미 조인 유도
select deptno, dname, loc
  from dept d
 where exists ( select /*+ unnest hash_sj */ 'x' 
                  from emp 
                 where deptno = d.deptno)
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- MATERIALIZE
-- ▲▲▲▲▲▲▲▲▲▲▲
-- WITH 문으로 정의한 집합을 물리적으로 생성
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- INLINE
-- ▲▲▲▲▲▲▲▲▲▲▲
-- WITH 문으로 정의한 집합을 INLINE 처리
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- MERGE
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 뷰 머징 유도
select /*+ leading(d) use_nl(e) merge(e) */ e.deptno, d.dname, e.cnt, e.avg_sal
  from dept d, (select deptno, count(*) cnt, avg(sal) avg_sal 
                  from emp 
                 group by deptno) e
 where d.deptno = e.deptno
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- NO_MERGE
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 뷰 머징 방지
select /*+ leading(d) use_nl(e) no_merge(e) */ e.deptno, d.dname, e.cnt, e.avg_sal
  from dept d, (select deptno, count(*) cnt, avg(sal) avg_sal 
                  from emp 
                 group by deptno) e
 where d.deptno = e.deptno
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- UNNEST
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 서브쿼리 Unnesting 유도 (서브쿼리가 Join으로 풀림)
select deptno, dname, loc
  from dept d
 where exists ( select /*+ unnest */ 'x' 
                  from emp 
                 where deptno = d.deptno)
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- NO_UNNEST
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 서브쿼리 Unnesting 방지 (서브쿼리가 Filter로 풀림)
select deptno, dname, loc
  from dept d
 where exists ( select /*+ no_unnest */ 'x' 
                  from emp 
                 where deptno = d.deptno)
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- PUSH_PRED
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 조인 조건 Pushdown 유도
-- 조인 조건 push down : 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣은 것
-- view merging 발생하면 쿼리 블록이 병합되므로, push_pred 수행 불가 함
-- push_pred 사용시 no_merge 힌트와 함께 사용
select /*+ leading(d) use_nl(e) no_merge(e) push_pred(e) */ e.deptno, d.dname, e.cnt, e.avg_sal
  from dept d, (select deptno, count(*) cnt, avg(sal) avg_sal 
                  from emp 
                 group by deptno) e
 where d.deptno = e.deptno
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- NO_PUSH_PRED
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 조인조건 Pushdown 방지
select /*+ leading(d) use_nl(e) no_merge(e) no_push_pred(e) */ e.deptno, d.dname, e.cnt, e.avg_sal
  from dept d, (select deptno, count(*) cnt, avg(sal) avg_sal 
                  from emp 
                 group by deptno) e
 where d.deptno = e.deptno
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- USE_CONCAT
-- ▲▲▲▲▲▲▲▲▲▲▲
-- OR 또는 IN-List 조건을 OR-Expansion으로 유도
-- PK_EMP(empno), EMP_X1(detpno) 조건절을 선두컬럼으로 인덱스 존재하므로 분기가능
select /*+ use_concat */*
  from emp
 where empno = 7900
    or deptno = 30
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- NO_EXPAND
-- ▲▲▲▲▲▲▲▲▲▲▲
-- OR 또는 IN-List 조건에 대한 OR-Expansion으로 방지
select /*+ no_expand */*
  from emp
 where empno = 7900
    or deptno = 30
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- PARALLEL
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 테이블 또는 DML을 병렬방식 처리 유도
select /*+ full(e) parallel(e 8) */ * from emp e;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- PARALLEL_INDEX
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 인덱스 스캔을 병렬방식으로 유도
select /*+ index_ffs(e pk_emp) parallel_index(e pk_emp 2) */ count(1) from emp e;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- PQ_DISTRIBUTE
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 병렬 수행 시 데이터 분배 방식 결정
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- APPEND
-- ▲▲▲▲▲▲▲▲▲▲▲
-- Direct-Path Insert 유도
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- DRIVING_SITE
-- ▲▲▲▲▲▲▲▲▲▲▲
-- DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지정
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- PUSH_SUBQ
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 서브쿼리를 가급적 빨리 필터링 하도록 유도
select deptno, dname, loc
  from dept d
 where exists ( select /*+ no_unnest push_subq(e) */ 'x' 
                  from emp e
                 where deptno = d.deptno)
;
 
-- ▼▼▼▼▼▼▼▼▼▼▼
-- NO_PUSH_SUBQ
-- ▲▲▲▲▲▲▲▲▲▲▲
-- 서브쿼리를 가급적 늦게 필터링 하도록 유도
select deptno, dname, loc
  from dept d
 where exists ( select /*+ no_unnest no_push_subq(e) */ 'x' 
                  from emp e
                 where deptno = d.deptno)
;