오라클 쿼리 힌트

힌트(Hint) : dbms에게 SQL의 최적화를 위해 힌트를 주는 것. 데이터베이스관리자나 개발자가 오라클 내부 Optimizer가 선택한 explain plan(실행 계획)을 인위적으로 조정할 수 있는 방법.

 

Oracle 힌트란

/* */로 둘러싸여 있음. 단순 주석이 아닌 힌트로 특정 실행계획을 변경하는 지시어

힌트 작동 방식은 SQL 쿼리를 처리할 때 비용 기반 최적화를 수행함. 비용을 계산해 가장 효율적인 실행 계획을 결정함.

 

Oracle 힌트 사용법

힌트는 오라클 Optimizer에게 힌트를 준다고 생각해도 무방함. 사용하는 방법은 /* + */ 안에 지정하는 형태임.

만약 /*+ordered*/ 라고 했다면 테이블이 조인되는 순서를 정하는 효과가 난다. from절에 테이블 순서대로 조인

하라는 의미이다.

SELECT /*+ INDEX(emp emp_ix) LEADING(dept) USE_NL(dept) */ emp_name, dept_name
FROM employees emp, departments dept
WHERE emp.dept_id = dept.dept_id
;

Colored

 

힌트 유형 및 종류

구분	Hint	의미
최적화 목표	/*+ALL_ROWS */	전체 처리 속도 최적화
	/*+FIRST_ROWS(N) */	최초 N건 응답속도 최적화
	/*+CHOOSE*/	테이블의 통계정보 유무에 따라 규칙기반 또는 비용기반으로 최적화
	/*+RULE*/	규칙기반 옵티마이저를 이용 최적화
Index Scan 방식	/*+FULL(<Table명>) */	TABLE FULL SCAN 유도
	/*+INDEX(<Table><Index>) */	INDEX SCAN 유도
	/*+ NO_INDEX(<Table><Index>) */	지정한 인덱스 외 다른 인덱스 유도
	/*+ INDEX_DESC(<Table><Index>) */	INDEX를 역순으로 Scan하도록 유도
	/* + INDEX_FFS(<Table><Index>) */	INDEX FAST FULL SCAN 유도
	/* + INDEX_SS(<Table><Index>) */	INDEX SKIP SCAN 유도
Join 순서	/* + ORDERED */	FROM절에 나열된 순으로 Join
	/* + LEADING(<Table1><Table2>..) */	Hint 괄호 내 기술 순으로 Join
	/* + SWAP_JOIN_INPUTS(<Table>) */	HASH Join에서 BUILD INPUT을 지정
Join 방식	/*+ USE_NL(<Table>) */	NESTED LOOP Join을 지시
	/*+ NO_USE_NL(<Table>) */	NESTED LOOP Join이 아닌 방식을 지정
	/*+ USE_NL_WITH_INDEX(<Table><Index>) */	NESTED LOOP Join을 하면서 인덱스 사용 지시
	/*+USE_MERGE(<Table>) */	SORT MERGE Join을 지시
	/*+USE_HASH(<Table>) */	HASH Join을 지시
	/*+NO_USE_HASH(<Table>) */	HASH Join이 아닌 방식을 지시
Sub-Query 처리 방식	/*+PUSH_SUBQ */	메인쿼리부터 순서대로 진행되는 것이 아니라 서브쿼리가 먼저 처리되게 지시
	/*+NO_UNNEST */	서브쿼리를 Filter방식으로 처리되게 지시
	/*+UNNEST */	Filter방식이 아닌 조인 방식으로 처리하게 지시 - 아래 4개 힌트는 unnest와 함께 사용하는 힌트
	/*+NL_SJ */ (w/ UNNEST)	EXIST, IN에서 NESTED LOOP Join SEMI 처리
	/*+NL_AJ */ (w/ UNNEST)	EXIST, IN에서 NESTED LOOP Join ANTI 처리
	/*+HASH_SJ */ (w/ UNNEST)	EXIST, IN에서 HASH Join SEMI 처리
	/*+HASH_AJ */ (w/ UNNEST)	EXIST, IN에서 HASH Join ANTI 처리
병렬 처리	/*+PARALLEL(<Table><병렬수>) */	병렬수 만큼 병렬처리 수행 지시
	/*+NOPARALLEL(<Table>) */	병렬처리 하지 않고 테이블 액세스 수행
	/*+PQ_DISTRIBUTE(<Table>) */	병렬처리 할당을 위한 힌트
	/*+PARALLEL_INDEX(<Index><병렬수>) */	병렬수 만큼 인덱스 병렬처리 지시
	/*+ NOPARALLEL_INDEX(<Index>) */	병렬처리 하지 않고 인덱스 수행

 

정리

오라클 힌트를 사용하면 특정 상황에서 쿼리 성능을 크게 개선할 수 있지만, 항상 옵티마이저의 기본 동작을 신뢰하고,

힌트는 필요한 경우에만 신중하게 사용해야 함.

 

+ 힌트 말고 일반적인 SQL 튜닝 기법

1. 바인드 변수를 사용한다.(상수 값이 변경되어도 이전 질의를 사용함으로 최적화 실행 단계를 줄이기 때문)

2. 가급적 WHERE 절의 조건에 인텍스 칼럼을 모두 사용한다.

3. 인덱스 칼럼에 사용하는 연산자는 가급적 =을 사용한다.

4. 인덱스 칼럼은 변형을 사용하지 않는다.

5. OR보다는 AND를 사용한다.

6. HAVING 보다는 가급적 WHERE 절에서 데이터를 필터링한다.

7. DISTINCT는 가급적 사용하지 않는다.