[DB] 인덱스(Index) 사용 예시 (생성, 조회, 삭제, 리빌드)

들어가면서..

인덱스를 사용하는 방법을 알아보려 한다. 예전에 실습용으로 잠깐 만들어 두었던 B 테이블을 가지고 간단한 예제를 진행해 보려고 한다.

---

 인덱스(Index) 사용 예시 

① 인덱스 생성

--문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)

--예제 1) 기본
CREATE INDEX EX_INDEX ON B(B_NO, B_TITLE);

--예제 2) 컬럼 값 중복 허용 x 인 인덱스 생성
CREATE[UNIQUE] INDEX EX_INDEX ON B(B_NO, B_TITLE);

위와 같이 쿼리문을 작성하면 INDEX를 생성할 수 있다. CREATE 뒤에 UNIQUE 키워드를 붙이면 컬럼 값에 중복 값을 허용하지 않는다는 뜻이다.

② 인덱스 조회

--문법
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = '테이블명';

--예제
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';

인덱스를 생성하면 USER_INDEXES 시스템 뷰에서 조회할 수 있다. 방금 B 테이블에 만들었던 EX_INDEX가 두 번째 ROW에 있는 것을 볼 수 있다.

💡 여기서 잠깐!

오라클은 테이블 정의 시 PRIMARY KEY와 UNIQUE KEY 제약조건을 정의할 때 자동으로 생성한다.
-> PK, UNIQUE 컬럼 자동으로 색인 생성.

따라서 B PK라는 인덱스가 이미 생성되있는 것을 알 수 있다.

③ 인덱스 삭제

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

--예제
DROP INDEX EX_INDEX;

인덱스는 조회 성능(SELECT)을 극대화하기 위해 만든 객체다. 그러나 너무 많이 만들면 INSERT, UPDATE, DELETE 시  부하가 발생해 전체적인 데이터베이스 성능을 저하하게 만든다. 따라서 안 쓰는 인덱스는 삭제시키는 것이 좋다.

---

 인덱스 리빌드(Rebuild) 사용 예시 

- 인덱스 리빌드를 하는 이유

DB의 쿼리 수행 속도 저하의 문제 중 하나는 쿼리 튜닝이 문제일 수도 있지만, 생성한 인덱스에서 INSERT, UPDATE, DELETE 등의 작업을 반복하면서 인덱스의 밸런스가 깨졌을 경우의 가능성도 있다.

인덱스의 밸런스가 깨졌다는 의미는
생성된 인덱스는 트리구조를 가지는데 데이터들이 무작위로 삽입, 수정, 삭제 등이 오랫동안 일어나면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어진다.

이것을 인덱스의 밸런스가 깨졌다고 얘기하고, 이러한 현상으로 인해 인덱스의 검색 속도가 떨어지므로 주기적인 리빌딩 작업을 하는 것이 좋다.

- 인덱스 리빌드 할 대상 조회 쿼리

SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL,
       DECODE(SIGN(NVL(I.BLEVEL,99)-3), 1, DECODE(NVL(I.BLEVEL,99), 99, '?', 'Rebuild'), 'Check') CNF
FROM USER_INDEXES I
WHERE I.BLEVEL > 4
ORDER BY I.BLEVEL DESC
;

해당 쿼리는 Index 트리의 깊이가 4 이상인 Index를 조회하는 쿼리다. 해당 쿼리문을 실행하여 검색되는 Index가 있다면 리빌딩을 하는 것이 좋다. 

- 인덱스 리빌드

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

--예제
ALTER INDEX EX_INDEX REBUILD;

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

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

---

[ Reference ]

https://coding-factory.tistory.com/419

https://tipland.tistory.com/5