[Oracle] Index Hint, 오라클 인덱스 힌트, Create Index, Alter Index, Drop Index - 범상치 않은 블로그

Oracle Index Hint

오라클과 같은 데이터베이스는 성능 최적화(optimizing)를 위해 힌트(hint)를 지시할 수 있습니다.

해당 예시 자료에서는 INDEX (INDEX_ASC 와 동일) 를 이용하여 인덱스 힌트를 사용하는 방법에 대해 안내해드릴 예정이며, 이외에도 여러 힌트가 존재하는 점, 참고해 주세요!

테이블의 컬럼을 업데이트 하는 작업을 진행하려고 합니다. 그런데 업데이트 구문에 대한 실행시간이 과도하게 느린 것을 확인하였습니다. 그리하여 해당 사항을 점검하기 위해 실행계획(explain plan)을 보았더니, TABLE ACCESS FULL 로 실행하고 있다는 것을 확인하였습니다.

이와 같은 상황에서 인덱스 힌트를 설정하여 속도를 개선하도록 진행해보겠습니다.

오라클 힌트 사용법

힌트를 사용하는 방법은 /*+ INDEX(tableName indexName) */ 과 같이 주석을 추가하는 것입니다. 다음의 방법을 따라해 보세요! 적용 방법은 주석 사이에 + 와 힌트를 작성하는 것입니다.

+는 주석시작부분 바로 뒤에 적으셔야 합니다. 한 칸 띄우시면 안 됩니다.(띄우면 오라클이 힌트로 인식하지 못합니다.)

+기호 이후 INDEX는 붙여도 되고 띄워도 됩니다.

tableName 과 indexName 는 공백으로 구분합니다. 적어도 1칸은 띄워주세요.

--+ INDEX(tableName indexName)

위와 같은 방법으로 힌트를 설정하여도 되지만 추천하지는 않습니다. 엔터키가 제대로 안 되었거나, 실수로 백스페이스로 한 줄을 지운 경우, 실제 쿼리 부분까지 주석처리가 될 가능성이 있기 때문입니다.

힌트를 여러 개 사용하는 경우, 한 칸 띄어서 덧붙여서 작성하면 됩니다.

/*+ INDEX(tableName indexName) INDEX(tableName2 indexName2) */

(가운데 INDEX 힌트는 띄워도 되고 안 띄어도 됩니다)

UPDATE TB_ORDER
   SET ODRDOC = DOC
 WHERE     DAT >= '20210126'
       AND DAT <= '20210126'
       AND NVL (DOC, ' ') <> 'B'
       AND ODRDOC = 'B';
                     
UPDATE /*+ INDEX(TB_ORDER INX_ORDER_03) */
      TB_ORDER
   SET ODRDOC = DOC
 WHERE     DAT >= '20210126'
       AND DAT <= '20210126'
       AND NVL (DOC, ' ') <> 'B'
       AND ODRDOC = 'B';

오라클 힌트 정리

오라클 데이터베이스에서 인덱스 객체에 관해 알아보는 시간입니다. 다음과 같은 목차에 따라 진행해 보도록 하겠습니다.

• Create Index (생성)
• Alter Index (수정)
• Drop Index (삭제)
• Select Index (조회)
• Explain Plan (인덱스 타는지?)
• Index Hint (힌트)

Create Index (인덱스 생성)

오라클에서 인덱스를 생성하는 방법에 대해 알아보도록 하겠습니다.

CREATE [UNIQUE] INDEX indexName ON [schema.]tableName(c1 [DESC] [, c2 [DESC], ..., cN [DESC]]);

해당 구문을 이용하여 다음과 같이 인덱스를 생성하실 수 있습니다.

• CREATE UNIQUE INDEX indexName ON userName.tableName(c1 DESC);
• CREATE INDEX indexNameC2 ON userName.tableName(c2);
• CREATE INDEX indexNameC1C2 ON userName.tableName(c1, c2);

고유(unique) 인덱스를 설정하는 경우, 인덱스가 무결성을 보장하는 역할도 합니다.

예를 들면 상기와 같이 C1 칼럼이 '1'~'10' 인 테이블에 C1이 '1' 인 데이터를 저장하면 ORA-00001: 무결성 제약 조건 위배 오류가 위와 같이 발생합니다. 즉, '1' 값은 이미 C1 칼럼에 존재하기에 무결성 제약 조건에 위배됩니다.

Alter Index (인덱스 수정)

인덱스에 대한 수정은 Alter 테이블로 진행합니다.

ALTER INDEX indexName ~

인덱스 수정 중에 가장 많이 사용하는 명령어는 리빌드 일 것입니다.

ALTER INDEX index_Name REBUILD;

DBA_INDEXES 테이블에서 alter index rebuild 명령어 추출하기

SELECT 'alter index ' || index_name || ' rebuild;' FROM DBA_INDEXES;

(DBA_INDEXES 테이블 관련은 아래에서 인덱스 조회부분에 추가 설명해드릴게요)

Drop Index (인덱스 삭제)

DROP INDEX indexName;

Select Index (인덱스 조회)

• SELECT * FROM DBA_INDEXES;
• SELECT * FROM ALL_INDEXES;
• SELECT * FROM USER_INDEXES;

DBA는 DBA권한으로 접근 가능한 전체 인덱스를 조회합니다.

• ALL은 현재 스키마에서 접근 가능한 모든 인덱스를 조회합니다.
• USER는 현재 스키마 내의 인덱스를 조회합니다.
• DBA, ALL, USER는 다른 스키마에서도 사용하실 수 있으니 알고 계시면 좋습니다.

예를 들면 SELECT * FROM DBA_TABLES; 명령은 DBA테이블을 모두 조회합니다.

Explain Plan (인덱스 타는지 확인하기)

인덱스를 타는지, 안 타는지? 확인은 explain plan 으로 합니다. TOAD 프로그램에서는 Ctrl+E 명령으로 제공합니다.

EXPLAIN PLAN FOR 명령으로 DBMS_XPLAN 을 조회할 수 있습니다.

EXPLAIN PLAN
   FOR
      SELECT * FROM TABLENAME;

SELECT * FROM TABLE (DBMS_XPLAN.display);

Index Hint (인덱스 힌트)

SELECT
      C1, C2, C3
  FROM TABLENAME ALIAS
 WHERE C1 = '1';

현재의 실행계획은 indexName 을 이용한 스캔을 진행하고 있군요.

특정 인덱스를 활용하도록 명령하는 것이 힌트 입니다.

SELECT
        /*+ INDEX(alias indexNameC1C2)*/
      C1, C2, C3
  FROM TABLENAME ALIAS
 WHERE C1 = '1';

[ indexNameC1C2 ] 인덱스를 이용하도록 힌트 명령어를 주었습니다.

(테이블에 앨리어스가 지정되어 있다면 앨리어스를 지정하시고 없으면 테이블 이름을 설정해 주세요)

SELECT
        /*+ INDEX(tableName indexNameC1C2)*/
      C1, C2, C3
  FROM TABLENAME ALIAS
 WHERE C1 = '1';

테이블 ALIAS 를 지정하는 경우 인덱스 또한 ALIAS로 지정하셔야 합니다.

[ ALIAS ] 가 아니라 테이블 이름 [ TABLENAME ] 을 지정하였더니 인식을 못하여 indexName 으로 스캔을 진행하는 모습입니다.

힌트 설정은 코멘트와 비슷하나 시작 부분을 반드시 /*+ 이렇게 붙여 주어야 합니다.

/* + 이런 식으로 * 과 + 를 한 칸이라도 띄우면 힌트가 아니라 코멘트로 인식하기 때문이랍니다

위의 사진은 +를 띄어쓰기하여 hint를 인식하지 않아서 indexName으로 스캔합니다.

여러 개의 인덱스를 정의하는 경우 힌트 내에서 띄어쓰기로 지정하면 됩니다.