Oracle Hint

[생짜초보]

1. 최적화 목표(GOAL) 제어힌트
ALL_ROWS	쿼리의전체 결과를 모두 수행 하는것에 대한 최적화를 목표로 최저비용의 실행계획을 수립하도록 유도	​
CHOOSE	엑세스 하는 테이블의 통계정보 유무에 따라 규칙기준 또는비용기준을 적용하여 최적화수행 통계정보를 참조할 수 있는경우 ALL_ROWS 방식으로 처리	​
FIRST_ROWS	최적 응답시간을 목표로 최저 비용의 실행 계획을 수립하도록 유도	​
RULE	규칙기준 옵티마이져를 이용하여최적화를 요구.	​
2. 조인순서 조정을 위한 힌트
ORDERED	FROM절에 기술한 순서대로 조인을 하도록 유도 LEADING 힌트와 함께 쓰면 LEADING힌트는 무시 ORDERED 는 엑세스 순서만 제시. 조인방법을 유도하는 힌트(USE_NL,USE_MERGE) 와 함께 쓰는게 보통	SELECT /*+ ORDERED USE_NL(A,B) */ FROM TAB1 A , TAB2 B
LEADING	FROM절의 테이블 순서와 상관없이 조인순서를 제어	SELECT /*+ LEADING(B,C) */ FROM TAB1 A , TAB2 B , TAB3 C
3. 조인방법 선택용 힌트
USE_NL	NESTED LOOP 조인을 유도하는 힌트. 조인순서가 아닌 조인 방식을 유도. ​ 보통 USE_NL 힌트 구문은 ORDERED 힌트 구문과 같이 사용되는데 USE_NL이 취하는 인자는 FROM절에서 두 번째 나오는 테이블 (비드라이빙 테이블, inner/probed table)을 명시해 주어야 한다. 인수로 사용되지 않은 첫 번째 테이블은 outer/driving table 이 되는 것 이다.	SELECT /*+ ORDERED USE_NL(B) */ FROM TAB1 A , TAB2 B
NO_USE_NL	NESTED LOOP 조인을 제외한 방식으로 유도 NESTED LOOP가 최적일 경우 무시가능	​
USE_NL_WITH_INDEX	USE_NL_WITH_INDEX 의 경우 /*+ USE_NL_WITH_INDEX (inner_table inner_table_index) */ 와 같이 사용된다. ​ 이를 테면 우측과 같은 쿼리의 의미는 I alias 에 해당하는 테이블을 Inner Table 로 NL Join 을 하되 item_product_ix 인덱스를 사용하라는 의미이다.	SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ * FROM orders h, order_items l WHERE l.order_id = h.order_id AND l.order_id > 2400; ​ 만약 위 Hint 를 사용하지 않는다면 아래와 같이 Hint 가 많아진다. SELECT /*+ LEADING(h) USE_NL(I) INDEX(I item_product_ix) */ * FROM orders h, order_items l WHERE l.order_id = h.order_id AND l.order_id > 2400;
USE_HASH	해쉬조인 방식으로 수행 되도록 유도.	SELECT /*+ ORDERED USE_HASH(B) */ FROM TAB1 A , TAB2 B
NO_USE_HASH	해쉬조이 방식을 제외한 다른 조인방식으로 유도	​
USE_MERGE	SORT MERGE 조인방식으로 수행 되도록 유도	SELECT /*+ ORDERED USE_MERGE(B) */ FROM TAB1 A , TAB2 B
4. 병렬처리 관련힌트
PARALLEL	엑세스 할때와 DML 처리할때 SQL의 병렬처리를 유도하는 힌트. ​ SQL튜닝을 하다하다 마지막으로 시도하는 것이 "병렬처리(Parallel Processing)" 입니다. 마지막으로 시도하는 이유는 이게 시스템 자원을 많이 쓰기 때문입니다. ​ 1개의 SQL문을 처리하기 위해 1개의 프로세스를 띄워서 일해야 하는데, 빨리 실행하기 위해 8개, 16개, 50개, 100개의 프로세스를 병렬로 동시에 띄워서 내 SQL을 처리합니다. 하지만, 이렇게 병렬로 실행하도록 만들어 놓은 SQL을 한꺼번에 여러명이 동시에 실행해버리면 시스템이 뻗을 수 있습니다. 당연히 병렬처리 기술을 사용할 때는 SQL이 동시에 많이 실행되는 OLTP 환경에서는 자제해야 하고, 큰 SQL을 From time to time 실행하는 DW 환경에서는 많이 사용합니다.	/*+ parallel */ /*+ parallel(10) */ /*+ parallel(EMP, 10) */ ​ SELECT /*+PARALLEL(8) */ FROM emp; 위와 같이 병렬프로세스 갯수를 지정하지 않을 수도 있고, 지정할 수도 있고, 테이블을 지정할 수도 있습니다. 단, 위 첫번째 경우처럼 병렬프로세스 갯수를 지정하지 않으면 병렬프로세스가 시스템디폴트 갯수만큼 기동 될 수 있습니다. 이게 좀 큽니다. 따라서 왠만하면 병렬프로세스 갯수를 지정하는 것이 좋습니다. 내가 병렬프로세스 갯수를 위와같이 10 이라고 줬다고 해서 반드시 10개가 뜨는 것은 아닙니다. Oracle 이 시스템 파라메타 설정에 따라 또는 시스템 자원 리소스 여유상황에 따라 자동으로 조정합니다. 따라서, 병렬프로세스가 얼마나 뜨는지 실제로 확인해 보는게 좋습니다. 이걸 확인해보는 가장 좋은 방법은 Trace 를 떠보거나 Realtime SQL Monitoring 을 이용하는 것입니다.
NOPARALLEL	PARALLEL 옵션이 부여된 테이블 엑세스시 해당 테이블의 PARALLEL 파라페터를 무시하고 병렬처리를 하지 않고 수행 하도록 유도	​
PQ_DISTRIBUTE	병렬조인의 속도를 향상시키기위해 슬페이브 프로세스 - 생산자 와 소비자-프로세스 사이에서 조인할 테이블의 로우를 서로 주고 받는 할당작업의 방법을 정의하는 힌트	​
PARALLEL_INDEX	Partition 인덱스에 대해 인덱스 범위 스캔을 병렬로 처리하도록 하는 힌트 입니다. 형식은 오른쪽과 같은데 처음 인수를 제외하고는 모두 생략가능 하며 세번째 인수는 병렬도, 네번째 인수값과 세번째 인수의 곱이 해당 쿼리의 최종적인 병렬도 값을 나타냅니다.	/*+ PARALLEL_INDEX ( table [index [, index]...] [{ , integer | , DEFAULT | , } [ , integer | , DEFAULT ]] ) */ ​ Select /*+ parallel_index(emp, idx_ename, 3, 2) */ * From emp Where ename = ‘홍길동’; ​ 파티션된 테이블이 아니더라도 아래처럼 index_ffs 힌트와 같이 사용되어 지정된 인덱스의 스캔을단일 블록이 아닌 멀티 블록으로 수행하도록 할 수도 있습니다. ​ Select /*+ parallel_index(emp idx_ename) index_ffs(emp idx_ename) */ count(*) From emp Where ename like ‘김%’;
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)을 실행 하도록 하는 힌트 ::쿼리재작성(Query Rewrite) 란? 쿼리 수행시 테이블 엑세스 방법과 실체뷰 엑세스 방법중 유리한 것을 선택하도록 쿼리를 변형하는 것	​
NO_REWRITE	쿼리재작성(Query Rewrite)을 하지않도록 하는 힌트	​
MERGE	뷰병합이 일어나지 않을때 적용가능 ::뷰병합 이란? 뷰의 엑세스를 최적화 하기위해 뷰쿼리에 사용된 원래 테이블을 최적으로 엑세스 하도록 문장을 변형시키는것.	​
STAR_ TRANSFORMATION	스타변형조인을 수행하도록 요구하는 힌트	​
FACT	스타변형 조인에서 팩트테이블을 지정하기 위해 사용하는 힌트	​
UNNEST	서브쿼리와 메인쿼리를 합쳐 조인 형테로 변형 하도록 하는 실행계획을 유도	​
7. 기타힌트
APPEND	NSERT문에서 사용하는 힌트. 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	MERG되지 않은 서브쿼리를 최대한 먼저 수행 하도록 요구하여 수행속도를 향상 머지가 안된 서브쿼리가 범위를 줄여줄 경우 최대한 앞에서 수행시켜 수행속도를 향상	​
QB_NAME	쿼리 블록에 이름을 부여하여 다른 힌트에서 참조할수 있도록 함	​