ORACLE|오라클 힌트(Hint)

/*+ 힌트 */




힌트를 한 줄 이상에 걸쳐 작성하고자 할 때

--+ 힌트




힌트를 한 줄에만 작성할 때

/*+ ALL_ROWS */

마지막으로 출력될 행까지 최소한의 자원을 사용하여 최대로 빨리 가져오게 하는 실행 계획을 세움.

INDEX RANGE SCAN 보다 FULL TABLE SCAN 을 하는 것이 유리하다고 판단되는 경우.

/*+ RULE */

규칙 기반 옴티마이저의 접근 방식을 채택하게 하는 힌트.

[우선순위]

1.  ROWID에 의한 단일 행 실행

2.  클러스터 조인에 의한 단일 행 실행

3.  HASH CLUSTER KEY에 의한 단일 행 실행

4.  UNIQUE KEY 또는 PRIMARY KEY에 의한 단일 행 실행

5.  클러스터 조인

6.  HASH CLUSTER KEY

7.  INDEX CLUSTER KEY

8.  결합 인덱스

9.  단일 컬럼 인덱스

10. 인덱스에 의한 컬럼의 BOUNDED RANGE

11. 인덱스에 의한 컬럼의 UNBOUNDED RANGE

12. SORT MERGE JOIN

13. 인덱스로 구성된 컬럼의 MAX 또는 MIN 처리

14. 인덱스로 구성된 컬럼의 ORDER BY

15. FULL TABLE SCAN

/*+ FIRST_ROWS (n) */

n 숫자만큼의 행이 빠르게 반환되는 실행 계획을 옵티마이저가 선택하도록 설정함.

이 힌트를 사용하면 FULL TABLE SCAN보다는 INDEX SCAN을 선호하며 SORT MERGE JOIN 보다는 NESTED LOOP JOIN을 선호하게 됨.

[주의]

DELETE, UPDATE를 비롯하여 그룹핑이나 정렬 작업을 수행하는 SELECT 문에서 사용할 경우 무시됨.

개발자가 직접 사용하는 경우는 거의 드물다.

/*+ FULL (table_name) */

IDEX SCAN이 적합하지 않음에도 불구하고 실행 계획에서 INDEX SCAN을 채택하고 있는 경우 특정 테이블에 대해 FULL TABLE SCAN을 하고자 할 때 설정함.

특정 인덱스를 사용하도록 지정함과 동시에, 인덱스를 읽는 방식을 오름차순 또는 내림차순 형태로 지정할 때 사용함.




/*+ INDEX_ASC (table_name index_name) */

일반적으로 INDEX 힌트가 오름차순을 사용하므로 이 힌트는 거의 사용하지 않는다.




/*+ INDEX_DESC (table_name index_name) */

MAX 함수 등의 기능을 대신하여 종종 사용된다.

-- MAX 함수를 사용하는 것과 같은 결과

select /*+ index_desc (emp emp_pk) */

       empno

  form emp

 where rownum = 1

;

-- 가장 큰 값부터 아래로 10개 가져오기

select /*+ index_desc (emp emp_pk) */

       empno

  form emp

 where rownum <= 10

;

/*+ INDEX_FFS (table_name index_name) */

INDEX FAST FULL SCNA을 하도록 유도함.

SQL에서 사용하고 있는 컬럼들이 모두 인덱스에 있어서 테이블을 검색하지 않고도 인덱스만으로 검색이 가능한 경우 사용할 수 있음.

/*+ ORDERED */

FROM 절에 기술된 테이블의 순서대로 조인하도록 함.

/*+ LEADING (table_name) */

/*+ LEADING (table_name table_name ...) */

FROM 절에 기술된 테이블 순서와 관계없이 힌트에 직접 조인 순서를 기술한다.

아래의 힌트는 단독으로 사용하는 경우도 있지만 대부분 ORDERED 나 LEADING 과 같은 조인 순서를 지정하는 힌트와 함께 사용하는 것이 일반적이다.

/*+ USE_NL (table_name) */

/*+ USE_NL (table_name table_name ...) */

NESTED LOOP 조인의 사용을 지정한다.

NESTED LOOP 조인을 사용하지 않기를 원하는 경우 NO_USE_NL 힌트를 사용하여 강제로 제어할 수 있다.

/*+ USE_HASH (table_name) */

/*+ USE_HASH (table_name table_name ...) */

HASH 조인의 사용을 지정한다.

NESTED LOOP 조인을 사용하지 않기를 원하는 경우 NO_USE_HASH 힌트를 사용하여 강제로 제어할 수 있다.




드라이빙 테이블의 검색 결과가 많은 경우, INNER TABLE 과 조인을 수행할 때 NESTED LOOP JOIN 보다는 HASH JOIN 을 수행하는 것이 유리하다.

/*+ PARALLEL (table_name dgree) */

하나의 작업을 여러개의 서버 프로세스가 동시에 병렬로 처리하도록 설정.

몇 개의 프로세스가 동시에 처리하게 할 것인지 설정할 수 있는데, 이 값을 dgree 라고 함.

[주의]

PARALLEL 힌트는 DBMS의 자원을 임의로 할당하여 사용하게 하는 역할을 하므로, 무분별하게 사용하는 것은 시스템 전체에 영향을 끼칠 수 있다. 따라서 개발자가 단독으로 힌트를 사용하기 보다는 DBA의 동의를 얻어 적용 여부를 결정해야 한다.

/*+ USE_CONCAT */

조건절의 OR 를 UNION ALL 형식으로 변형하여 실행하도록 제어하는 힌트.

반대로 조건절의 OR 를 UNION ALL 형식으로 변형하여 실행하지 못하도록 하는 힌트는 NO_EXPAND 이다.




이와 같은 힌트를 사용하여 일으키는 변형은 항상 일어나는 것이 아니라 옵티마이저가 비용을 계산한 결과 효율적이라고 판단할 때에만 일어난다.

/*+ MERGE */

/*+ NO_MERGE */

인라인 뷰(INLINE VIEW)의 처리가 메인쿼리와 MERGE 되지 않을 때 강제로 MERGE 하도록 하는 힌트.

뷰의 MERGE 란, 뷰를 통해 데이터를 가져오는 작업을 최적화하고자 메인쿼리의 조건들과 병합하여 최소한으로 테이블에 접근할 수 있도록 내부적으로 SQL 문장을 변형시키는 것을 의미한다.

많은 경우 뷰의 MERGE 로 성능이 개선되는 것을 볼 수 있으며, 반대로 뷰가 MERGE 되지 않아야 성능이 좋아지는 경우도 종종 발생한다. 이런 경우 NO_MREGE 힌트를 사용하면 뷰가 MERGE 되는 것을 장제로 막을 수 있다.

/*+ UNNEST */

/*+ NO_UNNEST */

서브쿼리와 메인쿼리를 합쳐 조인 형태로 실행 계획 변형을 유도하는 힌트.

서브쿼리와 메인쿼리를 합쳐 조인 형태로 실행 계획이 변경되는 것을 막고자 할 때는 NO_UNNEST 힌트를 사용할 수 있으며, 반대로 일부러 조인 형태로 변형하고자 할 때는 UNNEST 힌트를 사용하면 된다.

-- 서브쿼리에서 사용한 사원 테이블을 메인쿼리에서 사용하고 있는 부서 테이블과 직접 조인

select a.부서명

  from 부서 a

 where a.부서번호 in (select b.부서번호

                        from 사원 b

                       where b.부서번호 = a.부서번호)

;

-----------------------------------------------------

Id  Operation                          Name

-----------------------------------------------------

0   SELECT STATEMENT

1     NESTED LOOP

2       SORT UNIQUE

3         INDEX FULL SCAN              IDX_사원_부서

4       TABLE ACCESS BY INDEX ROWID    부서

5         INDEX UNIQUE SCAN            PK_부서

-----------------------------------------------------

-- 서브쿼리가 메인쿼리에 합쳐져 왼되는 것을 막고자 하는 경우

select a.부서명

  from 부서 a

 where a.부서번호 in (select /*+ NO_UNNEST */

                             b.부서번호

                        from 사원 b

                       where b.부서번호 = a.부서번호)

;

-------------------------------------

Id  Operation                Name

-------------------------------------

0   SELECT STATEMENT

1     FILTER

2       TABLE ACCESS FULL    부서

3       INDEX RANGE SCAN     사원_IDX

-------------------------------------

/*+ PUSH_PRED (inline_view_name) */

/*+ NO_PUSH_PRED (inline_view_name) */




뷰 외부에서 제시하고 있는 조인 조건을 뷰 안으로 들여오기 위해 사용하는 힌트.

/*+ DRIVING_SITE (table_name) */

분산 데이터베이스 환경에서 사용할 수 있는 힌트. 로컬 DB에 있는 테이블과 원격 DB에 위치한 테이블을 조인하여 데이터를 검색하고자 할 때, 조인이 수행될 위치를 지정함으로써 분산 SQL을 최적화 함.

-- SQL의 실행 주체는 해당 SQL을 실행시킨 로컬 DB임.

-- 즉, 원격 DB에 있는 데이터를 로컬로 가져와서 조인을 수행함.

select a.사번

     , a.사원이름

     , b.부서이름

     , b.근무지

  from 사원 a

       부서@REMOTE_DB b

 where a.부서번호 = b.부서번호

;

-- 조인 작업을 원격 DB에서 수행하도록 유도.

-- 조인이 수행될 위치를 원격 DB로 지정함으로써 분산 SQL을 최적화함.

select /*+ driving_site (b) */

       a.사번

     , a.사원이름

     , b.부서이름

     , b.근무지

  from 사원 a

       부서@REMOTE_DB b

 where a.부서번호 = b.부서번호

;

#출처: https://sites.google.com/site/smcgbu/home/gongbu-iyagi/hintjeonglijung