오라클 힌트(Oracle Hints)는 SQL 실행 계획을 최적화하기 위해 개발자가 직접 오라클 옵티마이저(Optimizer)에 지시를 내릴 수 있도록 하는 기능입니다. /*+ HINT */ 형식으로 SQL 문 내부에 주석 형태로 작성됩니다.
오라클 힌트 기본 문법
SELECT /*+ HINT_NAME */ column1, column2
FROM table_name;- /*+ HINT_NAME */ 부분에 적용할 힌트를 작성합니다.
- 여러 개의 힌트를 적용할 수도 있습니다.
주요 오라클 힌트 종류 및 예시
1) 실행 계획 관련 힌트
(1) FULL(table) – 전체 테이블 스캔 강제
SELECT /*+ FULL(emp) */ *
FROM emp
WHERE deptno = 10;- emp 테이블을 인덱스를 사용하지 않고 Full Table Scan하도록 유도합니다.
(2) INDEX(table index_name) – 특정 인덱스 사용
SELECT /*+ INDEX(emp emp_idx) */ *
FROM emp
WHERE deptno = 10;- emp 테이블의 emp_idx 인덱스를 사용하도록 유도합니다.
(3) NO_INDEX(table index_name) – 특정 인덱스 사용 금지
SELECT /*+ NO_INDEX(emp emp_idx) */ *
FROM emp
WHERE deptno = 10;- emp 테이블에서 emp_idx 인덱스를 사용하지 않도록 설정합니다.
(4) ROWID 접근 방식 강제
SELECT /*+ ROWID(emp) */ *
FROM emp
WHERE ROWID = 'AAACpWAABAAABSsAAA';- ROWID를 이용하여 데이터를 빠르게 접근합니다.
2) 조인 관련 힌트
(5) USE_NL(outer_table inner_table) – NL 조인 강제
SELECT /*+ USE_NL(emp dept) */ emp.*, dept.*
FROM emp, dept
WHERE emp.deptno = dept.deptno;- Nested Loop Join(NL 조인)을 강제 적용합니다.
(6) USE_HASH(outer_table inner_table) – 해시 조인 강제
SELECT /*+ USE_HASH(emp dept) */ emp.*, dept.*
FROM emp, dept
WHERE emp.deptno = dept.deptno;- 해시 조인(Hash Join) 방식으로 실행하도록 유도합니다.
(7) ORDERED – 조인 순서 강제
SELECT /*+ ORDERED */ emp.*, dept.*
FROM emp, dept
WHERE emp.deptno = dept.deptno;- FROM 절에 명시된 순서대로 조인을 수행합니다.
3) 병렬 처리 관련 힌트
(8) PARALLEL(table degree) – 병렬 쿼리 실행
SELECT /*+ PARALLEL(emp 4) */ *
FROM emp;- emp 테이블을 4개의 병렬 프로세스를 사용하여 조회합니다.
(9) NO_PARALLEL(table) – 병렬 실행 비활성화
SELECT /*+ NO_PARALLEL(emp) */ *
FROM emp;- emp 테이블을 병렬 처리를 하지 않고 단일 프로세스로 실행합니다.
4) 기타 최적화 힌트
(10) APPEND – INSERT 성능 향상
INSERT /*+ APPEND */ INTO emp_new
SELECT * FROM emp;- Direct Path Insert를 사용하여 데이터를 빠르게 삽입합니다.
(11) FIRST_ROWS(n) – 응답 시간 최적화
SELECT /*+ FIRST_ROWS(10) */ *
FROM emp
ORDER BY sal DESC;- 처음 10개의 행을 빨리 가져오는 방식으로 최적화합니다.
(12) ALL_ROWS – 전체 처리량 최적화
SELECT /*+ ALL_ROWS */ *
FROM emp
ORDER BY sal DESC;- 전체 실행 비용을 최소화하는 방식으로 최적화합니다.
복합 힌트 예제
(1) 특정 인덱스를 사용하고, 병렬 처리를 활성화하며, NL 조인을 강제
SELECT /*+ INDEX(emp emp_idx) PARALLEL(emp 4) USE_NL(emp dept) */
emp.*, dept.*
FROM emp, dept
WHERE emp.deptno = dept.deptno;- emp 테이블에서 emp_idx 인덱스를 사용
- emp 테이블을 4개 프로세스로 병렬 처리
- Nested Loop 조인 방식 적용
힌트 사용 시 주의점
- 옵티마이저가 항상 힌트를 따르는 것은 아님
- 옵티마이저는 힌트를 무시하고 최적화된 실행 계획을 선택할 수도 있습니다.
- 잘못된 힌트 사용 시 성능 저하 발생 가능
- 적절한 힌트를 사용하지 않으면 오히려 성능이 저하될 수 있음.
- 테스트 필수
- 실행 계획을 확인 (EXPLAIN PLAN)하여 힌트가 적용되었는지 반드시 테스트해야 합니다.
실행 계획 확인 방법
1) EXPLAIN PLAN 사용
EXPLAIN PLAN FOR
SELECT /*+ FULL(emp) */ *
FROM emp;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);- 실행 계획을 확인하여 힌트 적용 여부를 체크합니다.
2) AUTOTRACE 옵션 사용
SET AUTOTRACE ON;
SELECT /*+ FULL(emp) */ * FROM emp;- SQL 실행과 함께 실제 실행 계획을 확인합니다.