[ORACLE] INDEX HINT (인덱스 힌트, 주석달기, 성능 튜닝)

- 실무에서 데이터베이스 관련 일을 하다 보면 '인덱스를 탄다' '인덱스를 안 탄다'와 같은 말들을 들을 수 있다. 오라클 서버가 업그레이드되면서 Optimizer의 성능도 함께 향상되어 쿼리 실행 시 최적의 방법으로 실행해준다. 하지만 Optimizer가 항상 최적의 실행 경로를 만들어내는 것은 불가능하기 때문에 직접 실행 경로를 작성해 주어야 하는 경우가 생긴다. 따라서 DBA는 느린 쿼리나 성능 튜닝을 위해 Optimizer가 적절한 인덱스를 타고 있는지 확인하는 절차가 필요하다. 한 쿼리에서 어떤 인덱스의 선택도가 높은지 파악하고 Optimizer에 의존한 실행계획보다 효율적인 실행 계획을 만들어 내어야 한다.
- 힌트, 인덱스, 조인 등의 개념을 명확히 알지 않은 상태에서 무분별한 힌트의 사용은 데이터베이스 전체 성능의 저하를 일으킬 수 있다. (힌트에 오타가 있는 경우에는 오라클 서버가 잘못 사용된 힌트로 판단하여 힌트절을 무시하고 힌트 없이 쿼리를 수행한다.)

사용법

- SELECT 첫 줄에 힌트 주석을 작성하여 적절한 인덱스를 사용하도록 유도한다. (*/+......*/) 힌트 사용시 ORDER BY를 사용하지 않아도 인덱스 칼럼 순서로 정렬되어 조회된다.
- INDEX_ASC: 오름차순 인덱스 정렬
- INDEX_DESC : 내림차순 인덱스 정렬
- 싱글라인 주석 (--+), 멀티라인 주석 (/*+... */) 모두 인덱스를 사용할 수 있다.
- 여러개의 복합 인덱스 힌트를 사용할 수 있다. (/* + INDEX(...) INDEX(...) */)

테이블의 인덱스 조회

SELECT a.table_name 
     , a.index_name 
     , a.column_position 
     , a.column_name 
FROM user_ind_columns a 
WHERE a.table_name = '[테이블명]' 
ORDER BY a.index_name, a.column_position

- 위 예시의 EMP 테이블의 EMP_IDX02 인덱스는 COLUMN_POSITION를 기준으로 HIREDATE, DEPNO 두 개의 칼럼으로 구성되어 있다. 인덱스는 정의된 칼럼 순서대로 정렬되어 별도 지정된 영역에 저장되어 있다. 인덱스를 사용하게 되면 테이블에 바로 접근하지 않고 인덱스 영역에서 빠르게 ROWID를 찾아낸 후 실제 테이블에서 해당하는 데이터를 찾는다.

사용 예제

- 인덱스 힌트 절에 테이블명 또는 테이블 별칭을 사용할 수 있으나 되도록이면 테이블 별칭 사용을 권장함.

1. 일반적인 인덱스 힌트 사용

SELECT /*+ INDEX(EMP EMP_IDX02) */ 
    EMP.empno, 
    EMP.ename, 
    EMP.hiredate 
FROM EMP 
WHERE hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD') 
      AND TO_DATE('1981-12-31', 'YYYY-MM'DD');

2. 인덱스 칼럼 역순으로 데이터 조회

SELECT /*+ INDEX_DESC(EMP EMP_IDX02) */ 
    EMP.empno, 
    EMP.ename, 
    EMP.hiredate 
FROM EMP 
WHERE hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD') 
      AND TO_DATE('1981-12-31', 'YYYY-MM'DD');

3. 복합 인덱스 힌트 사용

SELECT /*+ INDEX(a EMP_PK) INDEX_DESC(b DEPT_PK) */ 
    , a.empno 
    , a.ename 
    , a.hiredate 
    , b.deptno 
    , b.dname 
FROM emp a, dept b 
WHERE a.empno = '7989' 
AND a.deptno = b.deptno;

4. 싱글 라인 주석 힌트 사용

SELECT --+ INDEX(EMP EMP_IDX02) 
    EMP.empno, 
    EMP.ename, 
    EMP.hiredate 
FROM EMP 
WHERE hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD') 
AND TO_DATE('1981-12-31', 'YYYY-MM'DD');

5. 비트맵 인덱스에서만 적용 가능한 힌트

SELECT /*+ INDEX_COMBINE(E) */ * 
FROM EMP E 
WHERE ename = ‘SMITH’
AND deptno = 10; 
Execution Plan ------------------------------------------------------------------- 
SELECT STATEMENT Optimizer=CHOOSE 
TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’ 
BITMAP CONVERSION (TO ROWIDS) 
BITMAP AND 
BITMAP INDEX (SINGLE VALUE) OF ‘bidx_emp_ename’ 
BITMAP INDEX (SINGLE VALUE) OF ‘bidx_emp_deptno’;

- 해당 테이블에 비트맵 인덱스가 존재하면, 비트맵 인덱스를 통한 접근을 유도한다.
- INDEX 명이 주어지지 않으면 Optimizer는 해당 테이블의 Best Cost로 선택된 Boolean Combination Index를 사용하며 INDEX명이 주어지면 주어진 특성 Bitmap Index의 Boolean Combination의 사용