[Oracle] 인덱스 - 리빌드

뜻하지 않게 운영업무를 맡게되어 내 업무가 될지 몰랐던,, oracle 관리 작업을 시작하였다.

현재 엑사데이터에 여유공간이 전혀 없이 풀로 사용하고 있어 언제라도 장비가 꺼지면 데이터는 다 날아가버리는 위기 상황이다..

저장공간 확보 대장정의 세번째! 인덱스 리빌드를 해보려한다..

 

---

인덱스 리빌드는 왜 해야하지?

인덱스와 테이블의 정보가 달라질 때 진행한다.

테이블이 insert/delete/update 될 때 인덱스도 같이 수정된다고 생각한다면 그건 바로 경기도 오산,,

테이블의 수정사항이 많다면 인덱스도 리빌드 해줘야한다!

• INSERT

INSERT SPLIT 현상이 발생할 수 있다!

INSERT SPLIT이란? 인덱스의 블럭들이 하나에서 두 개로 나눠지는 현상

 

기존 블록에 여유공간이 없는 상황에서 새로운 데이터가 들어오는 상황에 발생

1. 기존 블록의 내용 중 일부를 새 블록에 기록

2. 기존 블록에 빈 공간을 만들어 새로운 데이터를 추가

 

이로인해 속도가 느려질 수 있다.

 

• DELETE

테이블의 데이터를 DELETE 할 때 인덱스는 지워지지 않는다.

EX) 테이블 10만건, 인덱스 10만건 -> 테이블 5만건 삭제 -> 테이블 5만건, 인덱스 10만건

이 경우 테이블과 인덱스의 정보가 다르기 때문에 인덱스를 사용해도 속도가 느려질 수 있다.

 

• UPDATE

인덱스는 UPDATE 개념이 없다.

 

테이블에 UPDATE가 일어날 경우

1. 인덱스에서 DELETE 발생

2. 인덱스에 새로운 데이터 INSERT

 

두 가지 작업이 일어나기 때문에 더 부하를 줄 수 있다.

 

인덱스 리빌드의 영향

옵티마이져가 실행 계획을 세울 때 최근에 생성한 인덱스가 영향을 줄 수 있다.

*주의할것은! 리빌드하는 동안 테이블에 LOCK이 발생하기 때문에 업무시간 외에 진행하도록 하자!

---

인덱스 관리

 

1. 인덱스 조회

/* 인덱스 목록 */ SELECT * FROM USER_INDEXES; /* 인덱스에 포함 된 컬럼 리스트 */ SELECT * FROM USER_IND_COLUMNS;

 

2. 인덱스 사용 여부 모니터링

사용하지 않는 인덱스는 DROP/VISIBLE 한다.

/* 모니터링 시작하기 */ ALTER INDEX [인덱스명] MONITORING USAGE; /* 모니터링 중단하기 */ ALTER INDEX [인덱스명] NOMONITORING USAGE; /* 사용 유무 확인하기 */ SELECT * FROM V$OBJECT_USAGE;

 

3. 인덱스 밸런싱 확인

인덱스 밸런싱은 망가진? 인덱스가 몇%인지 확인하는 쿼리이다.

0에 가까울수록 좋다.

/* 인덱스 통계정보 생성 */ ANALYZE INDEX [인덱스명] VALIDATE STRUCTURE; /* 인덱스 밸런싱 확인 */ SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 AS BAL FROM INDEX_STATS;

 

4. 인덱스 리빌드

/* 인덱스 리빌드 */ ALTER INDEX [인덱스명] REBUILD;

 

---

궁금증

1. 운영 데이터 DELETE/INSERT 짱 많이 하는데 리빌드 한적 없음 알아서 되는건가

2. INSERT SPLIT이 인덱스의 문제라는건가

3. 영향 더 없나?

 

 

참고자료

https://lee-mandu.tistory.com/484

[오라클] 인덱스의 주의사항 및 관리방법(쿼리문 확인)

https://aozjffl.tistory.com/285

index rebuild 작업

https://jack-of-all-trades.tistory.com/400

오라클 Index 모니터링 - 안쓰는 인덱스 찾기 (index monitoring), Foreign Key 주의

https://dev-road.tistory.com/5

오라클 인덱스 생성/삭제/리빌드/조회