오라클 힌트(hint) 종류 및 사용법

『 '데이널'의 컨텐츠에 포함된 정보는? 』

 
이번 글에서는 오라클 힌트 종류 및 사용법에 대해 알아보겠습니다. 힌트(Hint)는 쉽게 말하면 dbms에게 SQL를 최적화를 위해 힌트를 주는 것입니다. 데이터베이스관리자(DBA)나 개발자가 오라클 내부 Optimizer가 선택한 실행 계획(explain plan)을 인위적으로 조정할 수 있는 방법입니다.
 
내 마음대로 컨트롤할 수 있다는 점에서 매력적이죠. 여러분이 Oracle Hint의 개념과 작동 방식을 이해한다면 힌트를 이용해 효율적인 쿼리 만들 수 있습니다.

주석이 아니고 힌트라고?

힌트는 Query Optimizer에 지시하기 위해서 SQL 쿼리에 포함된 주석처럼 생겼습니다. 오라클을 처음 배우는 친구들에게 설명해 줬더니, "이건 주석 아닌가요?" 라고 말해서 한바탕 웃었던 기억이 있습니다. 주석으로 착각한 이유는 /* */로 둘러싸여 있기 때문이었는데요. 오라클 쿼리에서 /*+ */는 단순 주석이 아닌 힌트로 특정 실행 계획을 변경하는 지시어라는 것을 알아야 합니다.
 

Oracle Hint 종류

 
힌트 작동 방식은 SQL 쿼리를 처리할 때 비용 기반(Cost based) 최적화 수행합니다. 비용을 계산해 가장 효율적인 실행 계획을 결정합니다. 이때 우리가 지정한 힌트를 명시적인 지침으로 지시하는 방식입니다. 그런데 가끔은 오라클이 자신이 더 맞다고 생각해 실행계획을 바꾸지 않는 경우도 있습니다.
 
오라클의 옵티마이저도 축적된 통계정보로 판단하기 때문에 대부분 맞는 결정을 합니다. 그렇다는 이야기는 때론 틀린 결정도 한다는 것이죠. 그래도 몇 가지 방법을 이용하면 우리의 지시대로 따르니 걱정하지 마세요. 
 

Oracle 힌트 사용법

힌트는 오라클 Optimzer에게 힌트를 준다고 생각해도 무방합니다. 사용하는 방법은 아래의 퀴리문과 같이 /*+  */ 안에 지정하는 형태로 사용합니다. 만약 /*+ 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
;

 

힌트 유형 및 종류

Oracle Hint는 다양한 형태로 제공되며 각각 쿼리 최적화의 다양한 측면을 다루고 있습니다. 일반적인 힌트 범주에는 최적화 목표, Index Scan 방식, 조인 순서, 조인 방식(Nest Loop, Sort Merge, Hash Join), 서브쿼리 처리방식, 병렬 처리등이 있습니다. 각 구분에 따른 자주 사용되는 힌트를 아래와 같이 정리해 보았습니다. 
 

구분	Hint	설명
최적화 목표	/*+ ALL_ROWS */	전체 처리속도 최적화
	/*+ FIRST_ROWS(N) */	최초 N건 응답속도 최적화
	/*+ CHOOSE */	테이블의 통계정보 유무에 따라 규칙기반 또는 비용기반으로 최적화
	/*+ RULE */	규칙기반 옵티마이저를 이용 최적화
Index Scan 방식	/*+ FULL(<Table명>) */	TABLE FULL SCAN 유도
	/*+ INDEX(<Table> <Index>) */	INDEX SCAN 유도
	/*+ NO_INDEX(<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 > */	병렬처리 하지 않고 인덱스 스 수행

 

마무리

Oracle Hints는 데이터베이스 최적화의 강력한 도구 역할을 합니다. Optimizer가 선택한 실행 계획에 직접적인 영향을 미칠 수 있는 수단이기 때문입니다. 힌트는 신중하게 사용하여야 합니다. 왜냐하면 특정 쿼리에서 성능이 크게 향상될 수 있지만 경우에 따라서는 유지 관리 오버헤드, 오용과 같은 위험이 따를 수도 있습니다.

rdbms 종류에 따라 힌트(Hint)도 다릅니다. 오라클의 힌트(Hint) 사용 기술에 익숙해진다면 다른 데이터베이스에서도 비슷하게 활용할 수가 있습니다. 한 줄 요약하면 SQL 최적화, 성능 튜닝을 위해 힌트를 이용하면 쿼리 효율성을 향상할 수 있습니다. 
 
만약 여러분의 힌트가 잘~ 적용되었는지를 알고 싶다면 실행 계획을 확인하면 됩니다. 옵티마이저의 SQL 실행 계획을 해석하는 방법은 아래를 참고하시기 바랍니다. 
 
* 함께 읽으면 좋은 글

실행 계획 보는 법 - SQL 튜닝