[Oracle] 인덱스(Index) 문법과 리빌드(Rebuild) 방법

[Oracle] 인덱스(Index)의 정의

 

인덱스의 경우 정의가 복잡해 두 개의 포스팅으로 나누었습니다.

인덱스의 정의는 위의 게시물을 참조하세요.

 

 

인덱스의 종류

 

 

 유일(UNIQUE) 인덱스 

- 컬럼의 유일성을 보장한다.

- 테이블에 하나 이상의 같은 데이터 값이 들어가는 걸 막아준다.

- 기본키(PRIMARY KEY)와 유일성 제약조건을 가진 컬럼에 대해 자동으로 생성하는 인덱스이다.

 

 비유일(NON-UNIQUE) 인덱스 

- 유일 인덱스와 정반대

- 참조되는 테이블 컬럼은 중복될 수 있다.

 

 복합(CONCASTENATED) 인덱스 

- 인덱스 생성시 한 테이블 안에 복수 컬럼을 지정한다.

 

 

---

인덱스 기본 문법

 

 

 인덱스 생성 

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명 (컬럼1, 컬럼2 ...);

 

UNIQUE => 인덱스에 대해서는 컬럼값에 중복 데이터를 허용하지 않음

 

 

 인덱스 조회 

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';

 

인덱스 생성을 하게 되면 시스템뷰에서 조회가능하다. 따로 인덱스를 만든 적이 없는데 추출 돼 있는 경우도 있다. 그것은 바로 PRIMARY KEY를 지정하면 UNIQUE 인덱스가 자동으로 지정되기 때문임!

 

 

 인덱스 수정 

ALTER INDEX IND_DEPTNO REBUILD;

 

 

 인덱스 삭제 

--문법
DROP INDEX [인덱스명];

--예제
DROP INDEX EX_INDEX;

 

인덱스의 경우 조회 성능을 극대화 시키기 위해서 만드는 객체인데 양이 많으면 INSERT, DELETE, UPDATE 등에 부하가 발생하기 때문에 전체적인 데이터베이스의 성능을 저하시킨다. 때문에 안쓰는 인덱스는 삭제하는 것이 좋음!

 

 

---

 

 인덱스를 리빌드하다? 

 

인덱스 파일은 INSERT, DELETE, UPDATE 등 반복하다보면 성능저하가 일어난다. 생성된 인덱스를 살펴보면 트리 구조를 가진다. 오랫동안 삽입-수정-삭제 과정을 거치게 되면 트리구조 한쪽이 무거워지고 성능 저하가 일어난다. 때문에 주기적으로 리빌딩을 하는 게 좋다.

 

--문법
ALTER INDEX [인덱스명] REBUILD;

--예제
ALTER INDEX PK_CUSTOMERS REBUILD;

 

이렇게 하나하나 리빌드 하는 게 귀찮을 경우 USER_INDEXES 안에 있는 인덱스를 조회해서 인덱스 리빌드 쿼리를 만들어 한 번에 실행 하는 게 편하다.

 

 

 

 전체 인덱스 리빌드 쿼리문 만들기 

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

      SELECT  'ALTER INDEX '||INDEX_NAME||' REBUILD; ' 
        FROM  USER_INDEXES 
      WHERE  TABLE_OWNER = 'C##ICIAUSER';       --사용자 ID
        --AND  INDEX_NAME LIKE 'PK_%' ;