[Oracle] INDEX HINT (성능 튜닝)

• target:  Index Hint를 통한 Select 쿼리 성능 개선

 

---

• method: 

주석인 /* */ 안에 +를 삽입함으로써 힌트 생성

오라클의 Optimizer가 쿼리 수행 전, 힌트를 참조하여 수행 (단, 힌트에 오타가 있는 경우에는 힌트절 무시)

SELECT /*+ INDEX(A IDX_A) */
       A.ID
     , A.NAME
     , A.REG_dATE
  FROM USER A

※ 주석 /* */ 안에 + 기호를 반드시 삽입 필요

※ 멀티라인 주석인 /* */ 외에도 싱글라인 주석인 --로도 사용 가능

※ 여러개의 복합 인덱스 힌트를 사용 가능 (ex. /*+ INDEX(...) INDEX(...) */)

※ 별도의 ORDER BY를 사용하지 않아도 INDEX 컬럼 순서로 정렬되어 조회(역순을 원할 경우 /*+ INDEX_DESC(...)*/ 사용)

 

---

INDEX를 사용하면, 테이블에 직접 접근하지 않고, 인덱스 영역에서 빠르게 ROWID를 찾아낸 후 실제 테이블에서 해당 데이터를 조회

테이블의 INDEX를 먼저 생성하고, INDEX 힌트를 통해서 해당 테이블 조회나 JOIN 시, INDEX 힌트 적용

(위의 예시에서 USER 테이블의 INDEX로 IDX_A가 미리 생성되어있어야 함)

-- 테이블의 인덱스 조회
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

 

---

비트맵 인덱스에서만 적용가능한 힌트도 있음

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 Index를 사용

 

---

SQL Developer의 쿼리 실행 계획 보기(F10)을 통해서 INDEX 힌트를 타는지 확인 필요

 

---

 

• warning: 무분별한 힌트 사용은 DB 전체 성능의 저하를 일으킬 수 있음

 

---

• source: 

https://kwomy.tistory.com/74

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