DevelopOracle

Oracle - 인덱스 힌트 사용법

Oracle 인덱스 힌트 사용법

오라클 옵티마이저가 최적의 인덱스를 선택하지 못할 때, 힌트를 사용하여 특정 인덱스 사용을 유도하는 방법을 다룹니다.

인덱스 힌트는 옵티마이저에게 특정 인덱스를 사용하도록 지시하는 강력한 도구입니다. 하지만 남용하면 오히려 성능을 저하시킬 수 있으므로 신중하게 사용해야 합니다.

INDEX 힌트 - 특정 인덱스 사용 강제

-- 기본 INDEX 힌트 사용
SELECT /*+ INDEX(e idx_emp_dept) */ *
FROM employees e
WHERE department_id = 10;

-- 여러 테이블에 각각 인덱스 힌트 적용
SELECT /*+ INDEX(e idx_emp_dept) INDEX(d idx_dept_loc) */
       e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
  AND d.location_id = 1700;

-- 복합 인덱스 사용 강제
SELECT /*+ INDEX(e idx_emp_dept_sal) */ *
FROM employees e
WHERE department_id = 10
  AND salary > 5000;

INDEX_DESC 힌트 - 인덱스 역순 스캔

-- 인덱스를 역순으로 스캔 (내림차순 정렬 효과)
SELECT /*+ INDEX_DESC(e idx_emp_salary) */ *
FROM employees e
WHERE salary > 5000
ORDER BY salary DESC;

-- 최근 데이터 조회 시 유용
SELECT /*+ INDEX_DESC(o idx_order_date) */ *
FROM orders o
WHERE order_date >= TRUNC(SYSDATE) - 30
ORDER BY order_date DESC;

INDEX_FFS 힌트 - Fast Full Index Scan

-- 테이블 액세스 없이 인덱스만으로 데이터 조회
SELECT /*+ INDEX_FFS(e idx_emp_dept_sal) */
       department_id, salary
FROM employees e
WHERE department_id IS NOT NULL;

-- COUNT 연산에 효과적
SELECT /*+ INDEX_FFS(e idx_emp_dept) */
       COUNT(*)
FROM employees e
WHERE department_id > 0;

INDEX_SS 힌트 - Index Skip Scan

-- 복합 인덱스의 선행 컬럼을 조건에 사용하지 않을 때
-- 인덱스: idx_emp_dept_sal (department_id, salary)
SELECT /*+ INDEX_SS(e idx_emp_dept_sal) */ *
FROM employees e
WHERE salary > 10000;  -- 선행컬럼 department_id 없이 사용

-- Skip Scan이 효율적인 경우: 선행 컬럼의 distinct value가 적을 때
SELECT /*+ INDEX_SS(e idx_gender_salary) */ *
FROM employees e
WHERE salary > 8000;  -- gender 컬럼(M/F 2개값)을 skip

INDEX_COMBINE 힌트 - 여러 비트맵 인덱스 결합

-- 여러 비트맵 인덱스를 결합하여 사용
SELECT /*+ INDEX_COMBINE(e idx_bmp_dept idx_bmp_job) */ *
FROM employees e
WHERE department_id = 10
  AND job_id = 'SA_REP';

-- 조건이 많을 때 효과적
SELECT /*+ INDEX_COMBINE(e) */ *  -- 모든 비트맵 인덱스 활용
FROM employees e
WHERE department_id = 10
  AND job_id = 'SA_REP'
  AND manager_id = 100;

INDEX_JOIN 힌트 - 인덱스 조인

-- 여러 인덱스를 조인하여 테이블 액세스 없이 데이터 조회
SELECT /*+ INDEX_JOIN(e idx_emp_dept idx_emp_salary) */
       department_id, salary
FROM employees e
WHERE department_id = 10
  AND salary > 5000;

NO_INDEX 힌트 - 특정 인덱스 사용 금지

-- 특정 인덱스 사용 방지
SELECT /*+ NO_INDEX(e idx_emp_dept) */ *
FROM employees e
WHERE department_id = 10;

-- 여러 인덱스 사용 방지
SELECT /*+ NO_INDEX(e idx_emp_dept idx_emp_salary) */ *
FROM employees e
WHERE department_id = 10
  AND salary > 5000;

FULL 힌트 - Full Table Scan 강제

-- 인덱스 대신 전체 테이블 스캔 강제
SELECT /*+ FULL(e) */ *
FROM employees e
WHERE department_id = 10;

-- 대량 데이터 조회 시 FTS가 더 효율적일 수 있음
SELECT /*+ FULL(e) */ COUNT(*)
FROM employees e
WHERE hire_date >= DATE '2020-01-01';

인덱스 힌트 조합

-- LEADING + INDEX 힌트 조합 (조인 순서 + 인덱스 지정)
SELECT /*+ LEADING(d e) INDEX(e idx_emp_dept) */
       e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
  AND d.location_id = 1700;

-- USE_NL + INDEX 힌트 조합 (Nested Loop + 인덱스)
SELECT /*+ USE_NL(e d) INDEX(e idx_emp_dept) INDEX(d pk_departments) */
       e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
  AND e.employee_id = 100;

-- PARALLEL + INDEX 힌트 조합
SELECT /*+ PARALLEL(e, 4) INDEX(e idx_emp_dept) */ *
FROM employees e
WHERE department_id = 10;

인덱스 힌트 사용 시 주의사항

-- 잘못된 예: 테이블 alias와 힌트 불일치
SELECT /*+ INDEX(employees idx_emp_dept) */ *  -- 잘못됨
FROM employees e
WHERE department_id = 10;

-- 올바른 예: alias 일치
SELECT /*+ INDEX(e idx_emp_dept) */ *  -- 올바름
FROM employees e
WHERE department_id = 10;

-- 인덱스 이름 오타 확인
SELECT /*+ INDEX(e idx_emp_dpt) */ *  -- 힌트 무시됨 (오타)
FROM employees e
WHERE department_id = 10;

-- 존재하는 인덱스인지 확인
SELECT index_name, table_name, column_name
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

힌트 효과 확인

-- 실행 계획에서 힌트 적용 여부 확인
EXPLAIN PLAN FOR
SELECT /*+ INDEX(e idx_emp_dept) */ *
FROM employees e
WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'TYPICAL'));

-- Note 섹션에서 힌트 사용 여부 확인
-- "- dynamic statistics used: dynamic sampling (level=2)"
-- "- this is an adaptive plan"

인덱스 힌트 우선순위

  1. INDEX(table index) - 가장 많이 사용, 특정 인덱스 강제
  2. INDEX_DESC - 역순 스캔 필요 시
  3. INDEX_FFS - 커버링 인덱스 활용
  4. INDEX_SS - 복합 인덱스의 후행 컬럼만 사용 시
  5. NO_INDEX - 잘못된 인덱스 선택 방지
  6. FULL - 대량 데이터 조회 시 FTS가 유리한 경우

실전 예제

-- 예제 1: 페이징 쿼리 최적화
SELECT /*+ INDEX_DESC(o pk_orders) */ *
FROM (
  SELECT /*+ FIRST_ROWS(10) */ o.*
  FROM orders o
  WHERE customer_id = 100
  ORDER BY order_date DESC
)
WHERE ROWNUM <= 10;

-- 예제 2: 복합 인덱스 활용
-- 인덱스: idx_emp_composite (department_id, job_id, salary)
SELECT /*+ INDEX(e idx_emp_composite) */
       employee_id, last_name, salary
FROM employees e
WHERE department_id = 10      -- 인덱스 1번째 컬럼
  AND job_id = 'SA_REP'       -- 인덱스 2번째 컬럼
  AND salary > 5000;          -- 인덱스 3번째 컬럼

-- 예제 3: 조인 쿼리 인덱스 힌트
SELECT /*+ LEADING(d e) USE_NL(e) INDEX(e idx_emp_dept) */
       e.employee_id,
       e.last_name,
       d.department_name
FROM departments d,
     employees e
WHERE d.department_id = e.department_id
  AND d.location_id = 1700;

-- 예제 4: 함수 기반 인덱스 힌트
-- 인덱스: idx_upper_lastname ON employees(UPPER(last_name))
SELECT /*+ INDEX(e idx_upper_lastname) */ *
FROM employees e
WHERE UPPER(last_name) = 'KING';

힌트를 사용하지 말아야 할 경우

  • 옵티마이저가 이미 최적의 실행 계획을 선택한 경우
  • 통계 정보가 정확하고 최신인 경우
  • 데이터 분포가 자주 변경되는 테이블
  • 애플리케이션 이식성이 중요한 경우
  • 힌트의 효과를 정확히 이해하지 못한 경우

권장사항: 힌트는 최후의 수단으로 사용하고, 먼저 통계 수집, 인덱스 추가, SQL 재작성 등을 시도하세요.

관련 문서

Share