[Oracle] 오라클 인덱스(Index) 힌트 사용방법 (주석, 튜닝)

오라클 버전이 업그레이드 되면서 옵티마이저의 성능도 많이 향상 되어 쿼리를 실행하면 최적의 인덱스를 선택하여 쿼리를 실행한다. 그러나 옵티마이저가 엉뚱한 인덱스를 선택하여 쿼리가 느려지는 경우가 있다.

 

실무에서 쿼리문를 작성하다보면 "인덱스가 안탄다", "인덱스를 태워야한다" 와 같은 얘기를 많이 듣는다. 아래는 어떻게 인덱스를 태워야하는지 간단히 정리한 내용이다.

 

오라클 인덱스 힌트

SELECT절 첫 줄에 힌트 주석(/*+  */)을 작성하여 적절한 인덱스를 부여하면 된다. 주석에 꼭 "+"를 붙여야 힌트절이 실행되며 "+"가 없으면 일반 주석으로 간주하고 아무런 이벤트가 없다.

 

SELECT /*+ INDEX(a EMP_IDX02) */
       a.empno
     , a.ename
     , a.hiredate
  FROM emp a

 

​인덱스 힌트 간단 정리

 

적절한 인덱스 힌트를 사용하면 쿼리의 수행 속도를 향상시킨다 ORDER BY를 사용하지 않아도 인덱스의 컬럼 순서로 정렬되어 조회된다 INDEX (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 = 'EMP' --테이블명
 ORDER BY a.index_name, a.column_position

 

 

EMP_IDX02 인덱스는 HIREDATE, DEPTNO 컬럼으로 구성되어 있으며 WHERE절에 HIREDATE 기준으로 데이터를 조회할 경우 사용한다. 만약 EMPNO로 조회한다면 EMP_PK 인덱스를 사용해야 한다.

 

인덱스는 정의된 컬럼의 순서대로 정렬되어 별도의 영역에 저장되어 있다. 그리고 인덱스를 사용할 경우 테이블에서 직접 조회하지 않고 인덱스 영역에서 빠르게 조회한 후 실제 테이블에서 해당 데이터를 찾는다.

 

쿼리 툴에서 실행 계획 보기

아래는 Oracle SQL Developer 쿼리 툴에서 실행 계획을 확인하는 장면이다. 대부분의 쿼리 툴에는 실행 계획을 확인하는 기능이 있으니, 인덱스를 타고 있는지 꼭 확인해야 한다.

 

 

 

사용 예제

 

예제 1 - 일반적인 인덱스 힌트를 사용하는 방법

 

 

예제 2 - 인덱스 컬럼의 역순으로 데이터를 조회하는 방법

ORDER BY를 사용하지 않고 인덱스 힌트를 사용하여 내림차순으로 정렬할 수 있다

 

 

예제 3 - 복한 인덱스 힌트를 부여하는 방법

여러개의 테이블을 조인하여 쿼리를 작성할 경우 인덱스 힌트를 여러개 부여할 수 있다

 

 

예제 4 - 인덱스 힌트 사용 시 테이블 명을 사용하는 방법

테이블 별칭이 없을경우 테이블 명을 사용할 수 있으나 되도록 테이블 별칭 사용을 권장한다

 

 

예제 5 - 싱글라인 주석 힌트 절 사용 방법