Oracle Hint 사용법 - 옵티마이저 제어

Oracle Hint는 SQL 실행 계획을 개발자가 직접 제어하는 방법입니다. 옵티마이저가 최적의 계획을 선택하지 못할 때 힌트를 사용하여 성능을 개선할 수 있습니다.

언제 사용하나요?

• 옵티마이저가 비효율적인 실행 계획 선택 시


• 특정 인덱스 강제 사용


• 조인 방식 지정


• 병렬 처리 제어

힌트 기본 문법

-- 단일 힌트
SELECT /*+ INDEX(emp idx_emp_dept) */ *
FROM employees emp
WHERE dept_id = 10;

-- 복수 힌트
SELECT /*+ INDEX(e idx_emp_dept) PARALLEL(e 4) */
       e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

-- 주의: /*+ 뒤에 공백 필요, 문법 오류 시 무시됨

인덱스 관련 힌트

-- 특정 인덱스 사용
SELECT /*+ INDEX(emp idx_emp_name) */ *
FROM employees emp
WHERE emp_name LIKE "김%";

-- 인덱스 사용 금지 (Full Scan 유도)
SELECT /*+ NO_INDEX(emp idx_emp_name) */ *
FROM employees emp;

-- Full Table Scan 강제
SELECT /*+ FULL(emp) */ *
FROM employees emp
WHERE dept_id = 10;

-- Index Fast Full Scan
SELECT /*+ INDEX_FFS(emp idx_emp_dept) */ dept_id
FROM employees emp;

-- Index Range Scan Descending
SELECT /*+ INDEX_DESC(emp idx_emp_date) */ *
FROM employees emp
WHERE hire_date > SYSDATE - 30;

조인 관련 힌트

-- Nested Loop Join (소량 데이터)
SELECT /*+ USE_NL(e d) */ 
       e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

-- Hash Join (대량 데이터)
SELECT /*+ USE_HASH(e d) */
       e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

-- Sort Merge Join
SELECT /*+ USE_MERGE(e d) */
       e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

-- 조인 순서 지정
SELECT /*+ LEADING(d e) USE_NL(e) */
       e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

-- 조인 순서를 테이블 나열 순서대로
SELECT /*+ ORDERED USE_NL(d) */
       e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

병렬 처리 힌트

-- 병렬 쿼리 (4개 프로세스)
SELECT /*+ PARALLEL(emp 4) */ *
FROM employees emp
WHERE status = "ACTIVE";

-- 병렬 DML
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(target 4) */ INTO target_table
SELECT /*+ PARALLEL(source 4) */ * FROM source_table;

-- 병렬 비활성화
SELECT /*+ NO_PARALLEL(emp) */ *
FROM employees emp;

서브쿼리 관련 힌트

-- 서브쿼리를 조인으로 변환
SELECT /*+ UNNEST */ *
FROM employees
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE location = "SEOUL"
);

-- 서브쿼리 변환 금지
SELECT /*+ NO_UNNEST */ *
FROM employees
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE location = "SEOUL"
);

-- 서브쿼리 먼저 실행
SELECT /*+ PUSH_SUBQ */ *
FROM employees
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE location = "SEOUL"
);

기타 유용한 힌트

-- 첫 N개 행 빠르게 반환
SELECT /*+ FIRST_ROWS(10) */ *
FROM employees
ORDER BY hire_date DESC;

-- 전체 처리량 최적화
SELECT /*+ ALL_ROWS */ *
FROM employees
WHERE status = "ACTIVE";

-- 캐시에 유지
SELECT /*+ CACHE(emp) */ *
FROM employees emp;

-- 결과 Append (Insert)
INSERT /*+ APPEND */ INTO target_table
SELECT * FROM source_table;

-- 뷰 병합
SELECT /*+ MERGE(v) */ *
FROM (SELECT * FROM employees WHERE status = "ACTIVE") v;

실행 계획 확인

-- 실행 계획 보기
EXPLAIN PLAN FOR
SELECT /*+ INDEX(emp idx_emp_dept) */ *
FROM employees emp WHERE dept_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 실제 실행 통계 확인
SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM employees WHERE dept_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    NULL, NULL, "ALLSTATS LAST"
));

힌트 사용 시 주의사항

• 힌트 문법 오류 시 무시됨 (에러 없음)


• 테이블 별칭 사용 시 별칭으로 힌트 작성


• 힌트보다 통계 정보 최신화가 우선


• 과도한 힌트는 유지보수 어렵게 함


• Oracle 버전에 따라 동작 다를 수 있음