오라클 SQL Hint 정리

 

1. 최적화 목표(GOAL) 제어힌트

ALL_ROWS : 쿼리의전체 결과를 모두 수행 하는것에 대한 최적화를 목표로 최저비용의 실행 계획을 수립하도록 유도.

CHOOSE : 엑세스 하는 테이블의 통계정보 유무에 따라 규칙기준 또는비용기준을 적용하여 최적화 수행. 통계정보를 참조할 수 있는 경우 ALL_ROWS 방식으로 처리.

FIRST_ROWS : 최적 응답시간을 목표로 최저 비용의 실행 계획을 수립하도록 유도.

RULE : 규칙기준 옵티마이져를 이용하여 최적화를 요구.

 

2. 조인순서 조정을 위한 힌트

ORDERED : FROM절에 기술한 순서대로 조인을 하도록 유도.  LEADING 힌트와 함께 쓰면 LEADING힌트는 무시.  ORDERED는 엑세스 순서만 제시. 조인방법을 유도하는 힌트. USE_NL, USE_MERGE와 함께 쓰는게 일반적.

ex>

SELECT /*+ ORDERED USE_NL(A,B) */
FROM TAB1 A , TAB2 B;

LEADING : FROM절의 테이블 순서와 상관없이 조인순서를 제어.

ex>

SELECT /*+ LEADING(B,C) */
FROM TAB1 A , TAB2 B , TAB3 C;

 

3. 조인방법 선택용 힌트

USE_NL : NESTED LOOP 조인을 유도하는 힌트. 조인 순서가 아닌 조인 방식을 유도.

NO_USE_NL : NESTED LOOP 조인을 제외한 방식으로 유도. NESTED LOOP가 최적일 경우 무시 가능.

USE_NL_WITH_INDEX : NESTED LOOP 조인에서 외측 루프의 처리주관 인덱스를 지정할때 사용.

USE_HASH : 해쉬 조인 방식으로 수행 되도록 유도.

NO_USE_HASH : 해쉬 조인 방식을 제외한 다른 조인 방식으로 유도.

USE_MERGE : SORT MERGE 조인 방식으로 수행 되도록 유도.

 

4. 병렬처리 관련힌트

PARALLEL : 엑세스 할때와 DML 처리할때 SQL의 병렬처리를 유도하는 힌트.

NOPARALLEL : PARALLEL 옵션이 부여된 테이블 엑세스시 해당 테이블의 PARALLEL 파라페터를 무시하고, 병렬처리를 하지 않고 수행 하도록 유도.

PQ_DISTRIBUTE : 병렬 조인의 속도를 향상시키기 위해 슬페이브 프로세스-생산자 와 소비자-프로세스 사이에서 조인할 테이블의 로우를 서로 주고 받는 할당작업의 방법을 정의하는 힌트.

PARALLEL_INDEX : 파티션 인덱스에 대한 인텍스 범위 스캔을 병렬로 수행하기 위한 병렬도를 지정하는 힌트.

NOPARALLEL_INDEX : 병렬 인텍스 범위 스캔을 하지않게 하는 힌트.

 

5. 엑세스수단 선택을 위한 힌트

FULL : 힌트 내에 정의된 테이블을 풀스캔 방식으로 유도.

HASH : 해쉬 클러스터 테이블을 엑세스 할때 해쉬 스캔 방식으로 유도.

CLUSTER : 클러스터링 테이블 엑세스시 클러스터 인덱스 스캔 방식으로 유도.

INDEX : 인덱스 범위 스캔에 의한 테이블 엑세스를 유도. 뷰의 경우 뷰 내의 테이블의 인덱스 스캔을 지정 할 수도 있다.

NO_INDEX : 지정한 인덱스외에 다른 엑세스를 고려하도록 유도. 테이블만 정의하면 모든 인덱스를 제외.

INDEX_ASC : 인덱스 컬럼값의 오름차순으로 범위스캔 하게 유도.

INDEX_DESC  : 인덱스 컬럼값의 내림차순으로 범위스캔 하게 유도.

INDEX_COMBINE : 2개 이상의 인덱스를 비트맵 인덱스로 변경/결합하여 엑세스 하는 방식으로 유도. 다른 타입의 인덱스도 변경/결합 가능.

INDEX_FFS : 전체범위를 스캔 하는 방식으로 유도. 멀티 블록을 스캔.

INDEX_JOIN : 2개 이상의 인덱스들로 조인을 수행하도록 유도. 인덱스만으로 쿼리를 처리할수 있어야 함.

INDEX_SS : 인덱스 스킵 스캔방식으로 엑세스 하도록 유도.

NO_INDEX_SS : 스킵 스캔을 제외한 다른 엑세스방법을 유도.

INDEX_SS_ASC : 인덱스 스킵 스캔방식으로 스캔 하는 경우 오름차순으로 인덱스를 읽도록 함.

INDEX_SS_DESC : 인덱스 스킵 스캔방식으로 스캔 하는 경우 내림차순으로 인덱스를 읽도록 함.

          

6. 쿼리 형태 변형을 위한 힌트

USE_CONCAT : OR(IN)연산자를 별도의 실행단위로 분리. 각각의 최적 엑세스경로를 수립후 연결 하는 실행 계획을 유도. 처리주관 조건이 OR 일경우만 사용가능. 잘못 사용시 비효율 발생.

NO_EXPAND : OR(IN)연산자를 연결실행계획으로 처리되지 않도록 유도.

REWRITE : 쿼리재작성(Query Rewrite)을 실행 하도록 하는 힌트.

NO_REWRITE : 쿼리재작성(Query Rewrite)을 하지 않도록 하는 힌트.

쿼리재작성(Query Rewrite) 란?

쿼리 수행시 테이블 엑세스 방법과 실체 뷰 엑세스 방법중 유리한 것을 선택하도록 쿼리를 변형하는 것.

MERGE : 뷰 병합이 일어나지 않을때 적용 가능.

뷰 병합 이란?

뷰의 엑세스를 최적화 하기 위해 뷰 쿼리에 사용된 원래 테이블을 최적으로 엑세스하도록 문장을 변형시키는 것.

STAR_TRANSFORMATION : 스타변형조인을 수행하도록 요구하는 힌트.

FACT : 스타변형조인에서 팩트 테이블을 지정하기 위해 사용하는 힌트.

UNNEST : 서브 쿼리와 메인 쿼리를 합쳐 조인 형테로 변형 하도록 하는 실행계획을 유도.

 

7. 기타 힌트

APPEND : INSERT문에서 사용하는 힌트. INSERT 작업을 ‘DIRECT-PATH’ 방식으로 수행시켜 SGA를 거치지 않고 직접 저장 공간에 입력 시킴. APPEND 힌트는 반드시 최고수위점 다음 위치에 데이터를 저장한다.

CACHE : 전체 테이블 스캔 방식으로 읽혀진 블록을 메모리내에 머물수 있도록 하는 힌트. 크기가 작은 테이블에 유용.

NOCACHE : LRU리스트의끝에 위치하도록 유도해서 메모리에서 우선적으로 제거되도록 하는 힌트.

CARDINALITY : 옵티마이져에게 카디널리티 값을 제시하여 실행 계획 수립에 참조하도록 하는 힌트.

CURSOR_SHARING_EXACT : CURSOR_SHARING 과 관련하여 실행계획 공유 비율을 높일수 있다.

DRIVING_SITE : 원격 테이블과 조회시 쿼리가 수행될 사이트를 지정하여 분산쿼리를 최적화하는 힌트.

DYNAMIC_SAMPLING : 통계 정보를 가지고 있지 않을 경우 통계 정보를 동적 표본화 하는 기능을 단위 SQL에 적용하는 힌트.

PUSH_PRED : 뷰 외부의 조인 조건을 뷰쿼리 내로 삽입하는 힌트.

NO_PUSH_PRED : 뷰 외부의 조인 조건을 뷰내로 삽입하지 않도록 함.

PUSH_SUBQ : MERGE 되지 않은 서브쿼리를 최대한 먼저 수행하도록 요구하여 수행 속도를 향상.

QB_NAME : 쿼리 블록에 이름을 부여하여 다른 힌트에서 참조 할 수 있도록 함.

REWRITE_ON_ERROR : 쿼리 재생성을 실행 할 수 없는 경우 ORA-30393 에러를 유발하여 쿼리 수행을 중단시킴.