[ORACLE] 인덱스 (Index) 조회, 사용 여부 조회 및 리빌드

인덱스는 트리 구조를 가지고 있으며 수정, 삽입, 삭제 등이 오랫동안 누적되면 트리의 깊이가 깊어지면서 조회 속도가 현저히 떨어집니다. 그렇기 때문에 주기적인 관리가 필수적입니다. 그렇다면 어떻게 인덱스를 관리할까요? 인덱스를 조회하는 방법부터 사용 여부 확인 및 리빌드 하는 방법에 대해서 알아보겠습니다.

 

인덱스 조회

SELECT * FROM USER_INDEXS [USER_IND_COLUMNS, ALL_INDEXS, ALL_IND_COLUMNS];

• USER_INDEXS : 현재 계정 소유의 인덱스 정보
• USER_IND_COLUMNS : 현재 계정 소유의 인덱스 컬럼 정보
• ALL_INDEXS : 전체 인덱스 정보
• ALL_IND_COLUMNS : 전체 인덱스 컬럼 정보

 

인덱스 사용 여부 조회 및 변경 (모니터링)

ALTER INDEX 인덱스명 MONITORING USAGE; // 인덱스 모니터링 설정

SELECT * FROM V$OBJECT_USAGE; // 모니터링 설정된 인덱스 조회

ALTER INDEX 인덱스명 MONITORING USAGE; // 모니터링 초기화

ALTER INDEX 인덱스명 NOMONITORING USAGE; // 모니터링 중지

------------------------------------------------------------------------------
ALTER INDEX 인덱스명 VISIBLE[INVISIBLE] // 인덱스 자동 사용[미사용]

사용 전/후	INDEX_NAME	USED	START_MONITORING	END_MONITORING
사용 전	인덱스명	NO	시작 시간	NULL
사용 후	인덱스명	YES	시작 시간	NULL
모니터링 중지	인덱스명	NO	시작 시간	종료 시간

조회하고자 하는 인덱스에 모니터링을 설정 후, 위와 같이 조회를 하면 모니터링 설정된 인덱스를 조회할 수 있습니다.

인덱스가 사용되면 USED 컬럼이 'YES'로 바뀌며, 모니터링을 초기화하면 'NO'로 다시 바뀌게 되고, 모니터링 설정을 중지하면 END_MONITORING 컬럼에 종료 시간이 출력됩니다.

 

사실 인덱스의 사용 여부를 조회하는 방법은 두 가지가 존재하는데, 앞에서 설명한 모니터링과 '실행 계획'입니다. 실행 계획에 대해서는 다른 포스팅에서 설명하는 걸로 하고 지금은 존재한다는 것만 알아두고 넘어가겠습니다.

 

인덱스는 데이터베이스 내의 옵티마이저가 최적의 길로 안내해서 자동으로 사용되어집니다. 수동으로 사용할 수 있는 방법은 없을까요? 아닙니다! 힌트를 이용하면 본인이 원할 때 언제든지 사용할 수 있습니다. 하지만 무분별하게 사용하면 오히려 조회 속도를 감소시키는 원인이 되기 때문에 제대로 숙지하고 사용하시는 걸 권장합니다.

SELECT /*+ INDEX [테이블명/인덱스명] */
	  COLUMN1
    , COLUMN2
FROM 테이블명
[WHERE ···]

주석 (/*  */)에서 +만 추가하면 힌트를 사용하는 방법입니다. 주석과 비슷해서 헷갈리기 쉬우니 사용하실 때 주의하셔야합니다. 힌트에 대한 자세한 내용은 다음 포스팅에서 설명하겠습니다.

 

인덱스 리빌드

1. ANALYZE 명령어를 사용해서 상태를 확인하고자 하는 인덱스의 상태를 분석한다.

ANALYZE INDEX 인덱스명 VALIDATE STRUCTURE; // 인덱스 상태 조회

Index '인덱스명'이(가) 분석되었습니다.

2. 아래와 같이 조회한다. 0에 가까울수록 좋은 상태를 의미하며, 20% 초과 시에 성능 저하로 간주되오니 해당 인덱스를 리빌드 해주는게 좋습니다.

SELECT (DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100 AS BALANCE
FROM INDEX_STATS;

3. 해당 인덱스 리빌드

ALTER INDEX 인덱스명 REBUILD;

※ 무수한 DML 작업과 무분별한 인덱스 사용을 오랫동안 방치하면 성능 저하를 일으킵니다. 주기적으로 리빌드해주는 것은 선택이 아닌 필수이며, 위와 같이 하나씩 검색해서 하는 방법은 굉장히 귀찮습니다. 전체 인덱스를 리빌드 하는 방법은 아래와 같습니다.

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD; ' FROM USER_INDEXS;

 

 

 

 

 

- 인덱스 조회 관련 포스팅 -

[ORACLE] 인덱스 (Index) 생성과 삭제 및 사용 시 주의사항