[Oracle] DDL(5) - 인덱스(INDEX)

이 게시물은 Database GUI Oracle SQL Developer의 아래 버전 정보를 기준으로 작성되었습니다. Java(TM) 플랫폼 : 1.8.0_321 Oracle IDE : 4.1.5.21.78

 

인덱스(INDEX)

 

(1) 인덱스의 개념

 

데이터베이스에서 데이터 검색 성능의 향상을 위해 테이블 열에 사용하는 객체. 테이블에 보관된 특정 열 데이터의 주소, 즉 일종의 색인 기능. 쿼리 속도 향상을 위해 사용하나, 과도한 사용은 데이터베이스 전체의 성능 부하를 가져올 수 있음

 

(2) 인덱스 생성

 

문법

 

CREATE [UNIQUE(선택)] INDEX [인덱스명] ON [테이블명]([열이름 혹은 열 관련 계산식1], [열이름 혹은 열 관련 계산식2], ... , [열이름 혹은 열 관련 계산식N]);

→ UNIQUE를 추가하면 컬럼 값에 중복을 허용하지 않음

 

사용 예시

CREATE INDEX IDX_EMP1 ON EMP(SAL);

CREATE INDEX IDX_EMP2 ON EMP(EMPNO, ENAME, SAL);

CREATE INDEX IDX_EMP3 ON EMP(SAL + COMM);

CREATE INDEX IDX_EMP4 ON EMP(SAL * 12);

CREATE UNIQUE INDEX IDX_EMP5 ON EMP(EMPNO);

 

(3) 인덱스 조회

 

문법

 

- 인덱스 정보 조회

SELECT * FROM USER_INDEXES;

→ 현재 사용자가 소유한 인덱스 정보 조회

 

SELECT * FROM ALL_INDEXES;

→ 현재 사용자가 접근할 수 있는 인덱스 정보 조회

 

SELECT * FROM DBA_INDEXES;

→ 데이터베이스의 모든 인덱스를 조회. 권한이 있어야 사용 가능. 권한은 추후 포스트에 다룰 예정.

 

- 인덱스 컬럼 정보 조회

SELECT * FROM USER_IND_COLUMNS;

→ 현재 사용자가 소유한 인덱스 컬럼 정보 조회
  
SELECT * FROM ALL_IND_COLUMNS;

→ 현재 사용자가 접근할 수 있는 인덱스 컬럼 정보 조회
  
SELECT * FROM DBA_IND_COLUMNS;

→ 데이터베이스의 모든 인덱스 컬럼 정보를 조회. 권한이 있어야 사용 가능.

 

 

사용 예시

SELECT * FROM USER_INDEXES;

 

현재 SYSTEM 계정이라 뭐가 엄청 많이 나온다.

SCOTT로 계정을 전환한 후 진행하니 텅 비어 있다.

 

 

다시 SYSTEM으로 전환 후 아래 쿼리를 실행해 보겠다.

 

SELECT * FROM USER_INDEXES WHERE INDEX_NAME LIKE '%IDX_EMP%';

위에서 생성한 인덱스를 확인할 수 있다.

 

 

(4) 인덱스명 수정

 

문법

 

ALTER INDEX [인덱스명] RENAME TO [바꿀 인덱스명];

 

 

사용 예시

ALTER INDEX IDX_EMP5 RENAME TO IDX_EMP5000;

SELECT * FROM USER_INDEXES WHERE INDEX_NAME LIKE '%IDX_EMP%';

 

 

(5) 인덱스 삭제

 

문법

 

DROP INDEX [인덱스명];

 

 

사용 예시

DROP INDEX IDX_EMP5000;

SELECT * FROM USER_INDEXES WHERE INDEX_NAME LIKE '%IDX_EMP%';

 

 

(6) 인덱스 리빌드(Rebuild)

 

설명

생성된 인덱스는 트리 구조를 가진다. 인덱스 생성 후 DML 쿼리들을 지속적으로 사용하면 트리의 깊이가 점점 깊어지면서 성능이 저하되기 시작한다.  이때 리빌드를 통해 성능을 다시 개선할 수 있다.

 

 

문법

 

ALTER INDEX [인덱스명] REBUILD;

 

 

사용 예시

ALTER INDEX IDX_EMP4 REBUILD;

 

 

아래 링크에 리빌드의 적절한 때를 확인하는 쿼리, 수많은 인덱스들의 리빌드 쿼리문을 간단하게 만드는 법이 잘 정리되어 있으니 참고하면 좋을 듯하다. 그 외에도 인덱스에 관한 정리가 상세히 되어 있다.

링크