[oracle] 인덱스 힌트(Index Hint)

1. 인덱스 힌트(Index Hint)

---

Oracle에서 인덱스 힌트(Index Hint)는 쿼리 옵티마이저가 특정 인덱스를 사용하도록 지시하는 힌트입니다. 인덱스 힌트를 사용하면 쿼리 성능을 최적화할 수 있으며, 옵티마이저가 잘못된 인덱스를 선택하는 것을 방지할 수 있습니다.

 

인덱스 힌트가 필요하지 않은 경우

• 옵티마이저의 최적화: 오라클 옵티마이저는 기본적으로 가장 효율적인 실행 계획을 선택합니다. 인덱스를 생성하면 옵티마이저가 이를 고려하여 최적의 실행 계획을 선택합니다.
• 쿼리가 단순한 경우: 쿼리가 단순하고 옵티마이저가 인덱스를 잘 사용하는 경우, 힌트를 추가할 필요가 없습니다.

인덱스 힌트가 유용한 경우

• 잘못된 실행 계획: 옵티마이저가 특정 인덱스를 사용하지 않거나, 잘못된 실행 계획을 선택하는 경우.
• 특정 인덱스 강제 사용: 특정 인덱스를 반드시 사용해야 하는 비즈니스 로직이 있는 경우.
• 쿼리 성능 튜닝: 쿼리 성능을 최적화하는 과정에서 특정 인덱스 사용을 강제하여 성능 개선을 확인하고자 하는 경우

다음으로는 인덱스 힌트를 사용하는 방법과 주요 개념에 대해 자세히 설명하겠습니다.

---

2. 인덱스 힌트의 기본 구문

---

인덱스 힌트는 주로 SELECT 문 내에서 사용되며, 특정 테이블에 대해 사용할 인덱스를 지정합니다. 기본 구문은 다음과 같습니다.

SELECT /*+ INDEX(table_alias index_name) */ column_list
FROM table_name table_alias
WHERE condition;

---

3. 인덱스 힌트의 주요 유형

---

1. INDEX 힌트
   • 특정 인덱스를 사용하도록 지시합니다.
   • 예: /*+ INDEX(emp emp_idx1) */
2. INDEX_DESC 힌트
   • 인덱스를 역순으로 사용하도록 지시합니다.
   • 예: /*+ INDEX_DESC(emp emp_idx1) */
3. INDEX_COMBINE 힌트
   • 비트맵 인덱스를 결합하도록 지시합니다.
   • 예: /*+ INDEX_COMBINE(emp emp_bmp_idx1 emp_bmp_idx2) */
4. INDEX_FFS 힌트
   • 고속 전체 스캔(Fast Full Scan)을 사용하도록 지시합니다.
   • 예: /*+ INDEX_FFS(emp emp_idx1) */
     

---

4. 인덱스 힌트 사용 예시

---

(1) 기본 사용

SELECT /*+ INDEX(emp emp_idx1) */ emp_id, emp_name
FROM employees emp
WHERE department_id = 10;

위 예제에서 INDEX(emp emp_idx1) 힌트는 옵티마이저가 employees 테이블의 emp_idx1 인덱스를 사용하도록 지시합니다.

(2) 다중 단일 인덱스 힌트 사용

SELECT /*+ INDEX(emp emp_idx1) INDEX(emp emp_idx2) */ emp_id, emp_name 
FROM employees emp 
WHERE department_id = 10 AND hire_date = '2020-01-01';

위 예제에서는 두 개의 인덱스 힌트를 사용하고 있습니다.

emp_idx1과 emp_idx2의 인덱스는 각각 단일 컬럼 인덱스입니다. 

(3-1) 복합 인덱스 사용

SELECT /*+ INDEX(emp emp_idx1) */ emp_id, emp_name
FROM employees emp
WHERE department_id = 10 AND hire_date = '2020-01-01';

여기서 emp_idx1은 department_id와 hire_date 컬럼을 포함하는 복합 인덱스라고 가정합니다.

(3-2) 다중 복합 인덱스 사용

-- 첫 번째 복합 인덱스: (department_id, hire_date)
CREATE INDEX idx_dept_hire ON employees(department_id, hire_date);

-- 두 번째 복합 인덱스: (department_id, salary)
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

SELECT /*+ INDEX(emp idx_dept_hire) INDEX(emp idx_dept_salary) */ emp_id, emp_name 
FROM employees emp
WHERE department_id = 10 AND hire_date = '2020-01-01';

-- 다른 쿼리 예시 (salary를 사용하는 경우)
SELECT /*+ INDEX(emp idx_dept_hire) INDEX(emp idx_dept_salary) */ emp_id, emp_name 
FROM employees emp
WHERE department_id = 10 AND salary > 50000;

'두 개의 복합 인덱스'는 서로 다른 컬럼 조합을 최적화하려는 경우에 사용됩니다. 이 경우 쿼리의 검색 조건에 따라 적절한 인덱스가 선택되거나 인덱스의 조합이 사용될 수 있습니다.

• idx_dept_hire는 department_id와 hire_date 조합에 대해 최적화된 인덱스
• idx_dept_salary는 department_id와 salary 조합에 대해 최적화된 인덱스

(4) 역순 인덱스 사용

SELECT /*+ INDEX_DESC(emp emp_idx1) */ emp_id, emp_name
FROM employees emp
WHERE department_id = 10
ORDER BY emp_id DESC;

INDEX_DESC 힌트는 인덱스를 역순으로 사용하도록 지시하여, 정렬 작업을 최적화합니다.

(5) 고속 전체 스캔 사용

SELECT /*+ INDEX_FFS(emp emp_idx1) */ emp_id, emp_name
FROM employees emp;

INDEX_FFS 힌트는 인덱스를 사용하여 테이블의 전체 스캔을 빠르게 수행하도록 지시합니다.

---

5. 복합 인덱스 사용 

---

INDEX 힌트를 사용하여 특정 테이블에 대해 여러 인덱스를 명시적으로 지정할 수 있습니다. 이 경우, 옵티마이저는 제공된 인덱스 중 하나를 선택합니다.

SELECT /*+ INDEX(emp emp_idx1) INDEX(emp emp_idx2) */ emp_id, emp_name 
FROM employees emp 
WHERE department_id = 10 AND hire_date = '2020-01-01';

여기서 emp_idx1과 emp_idx2는 employees 테이블의 인덱스입니다. 옵티마이저는 두 인덱스 중 하나를 선택하여 쿼리를 최적화합니다.

---

6. 주의사항

---

• 힌트를 사용할 때는 쿼리의 실행 계획을 확인하여 실제로 원하는 인덱스가 사용되는지 확인해야 합니다.
• 인덱스 힌트를 남용하면 오히려 성능이 저하될 수 있으므로, 필요한 경우에만 사용하는 것이 좋습니다.
• 힌트를 사용하더라도 옵티마이저는 다른 요소를 고려하여 힌트를 무시할 수 있습니다. 따라서 힌트는 쿼리 성능을 보장하는 절대적인 방법이 아닙니다.

인덱스 힌트를 적절히 사용하면 쿼리 성능을 향상시킬 수 있지만, 이는 데이터베이스 구조와 데이터 분포를 잘 이해하고 있을 때 가장 효과적입니다.

---