[Oracle] 오라클 인덱스 힌트 ( INDEX HINT)

인덱스는 데이터베이스 성능 최적화의 핵심 요소 중 하나입니다. 오라클에서는 인덱스 사용을 제어하기 위한 다양한 힌트를 제공합니다. 이 포스트에서는 주요 인덱스 관련 힌트들을 자세히 살펴보고, 실제 사용 예제를 통해 그 효과를 이해해 보겠습니다.

1. INDEX 힌트

INDEX 힌트는 옵티마이저에게 특정 인덱스를 사용하도록 지시합니다.

문법

/*+ INDEX(table_name index_name) */

예제

SELECT /*+ INDEX(employees emp_department_ix) */
    employee_id, department_id, first_name, last_name
FROM 
    employees
WHERE 
    department_id = 50;

이 예제에서는 employees 테이블의 emp_department_ix 인덱스를 사용하도록 지시하고 있습니다. 이 힌트는 특히 옵티마이저가 다른 실행 계획을 선택할 때 유용할 수 있습니다.

2. INDEX_FFS 힌트 (Fast Full Scan)

INDEX_FFS 힌트는 인덱스 fast full scan을 수행하도록 지시합니다. 이는 인덱스의 모든 블록을 읽지만, 정렬된 순서로 읽지 않습니다.

문법

/*+ INDEX_FFS(table_name index_name) */

예제

SELECT /*+ INDEX_FFS(employees emp_name_ix) */
    employee_id, first_name, last_name
FROM 
    employees
WHERE 
    first_name LIKE 'A%';

이 예제에서는 emp_name_ix 인덱스의 fast full scan을 수행하여 이름이 'A'로 시작하는 직원을 찾습니다. 이 방식은 많은 수의 로우를 반환해야 할 때 효과적일 수 있습니다.

3. NO_INDEX 힌트

NO_INDEX 힌트는 특정 인덱스 또는 모든 인덱스의 사용을 방지합니다.

문법

/*+ NO_INDEX(table_name index_name) */

예제

SELECT /*+ NO_INDEX(employees emp_department_ix) */
    employee_id, department_id, first_name, last_name
FROM 
    employees
WHERE 
    department_id = 50;

이 예제에서는 emp_department_ix 인덱스의 사용을 방지합니다. 이는 특정 인덱스가 비효율적일 때 유용할 수 있습니다.

4. INDEX_COMBINE 힌트

INDEX_COMBINE 힌트는 여러 인덱스의 부울 조합(Boolean combination)을 사용하도록 지시합니다.

문법

/*+ INDEX_COMBINE(table_name index1 index2 ...) */

예제

SELECT /*+ INDEX_COMBINE(employees emp_department_ix emp_job_ix) */
    employee_id, department_id, job_id, first_name, last_name
FROM 
    employees
WHERE 
    department_id = 50
    AND job_id = 'SA_REP';

이 예제에서는 department_id와 job_id 두 조건을 만족하는 직원을 찾기 위해 두 인덱스의 조합을 사용합니다.

5. INDEX_DESC 힌트

INDEX_DESC 힌트는 인덱스를 내림차순으로 스캔하도록 지시합니다.

문법

/*+ INDEX_DESC(table_name index_name) */

예제

SELECT /*+ INDEX_DESC(employees emp_department_ix) */
    employee_id, department_id, first_name, last_name
FROM 
    employees
WHERE 
    department_id > 50
ORDER BY
    department_id DESC;

이 예제에서는 emp_department_ix 인덱스를 내림차순으로 스캔하여 department_id가 50보다 큰 직원을 찾습니다. 이는 특히 내림차순 정렬이 필요한 경우에 유용합니다.

주의사항

힌트는 제안일 뿐이며, 옵티마이저가 더 나은 실행 계획을 찾으면 무시될 수 있습니다.
인덱스 힌트를 사용하기 전에 항상 실행 계획을 분석하고 테스트해야 합니다.
데이터 분포나 테이블 크기가 변경되면 힌트의 효과가 달라질 수 있으므로 주기적인 검토가 필요합니다.
과도한 힌트 사용은 유지보수를 어렵게 만들 수 있으므로 신중하게 사용해야 합니다.

인덱스 관련 힌트는 SQL 쿼리의 성능을 크게 향상시킬 수 있는 강력한 도구입니다. 하지만 그 사용에는 신중함이 필요합니다. 항상 데이터의 특성과 쿼리의 목적을 고려하여 적절한 힌트를 선택해야 하며, 실제 성능 향상 여부를 확인해야 합니다. 올바르게 사용된 인덱스 힌트는 복잡한 쿼리의 성능을 극대화하는 데 큰 도움이 될 수 있습니다.

개발