질문
[궁금한점] oracle에서 index를 주기적으로 재생성해야 하는 이유가 궁금합니다.
[상황설명] 어느 순간 쿼리 속도가 현저히 저하되어 인덱스가 깨졌나? 하여
index를 drop하고 다시 index를 create하면 속도가 다시 정상적으로 돌아옵니다.
속도가 떨어지는 테이블 구조는
number(5), date, number(5), float로 총 4개의 컬럼으로 이루어졌으며
데이터 핸들링은 1분마다 2000개의 데이터가 연속적으로
insert into [table] select * from; 으로 insert됩니다.
update는 일어나지 않고요. 다만 일주일이 지나면 일주일전 데이타를 delete합니다.
일주일간의 데이터수는 약 14,000,000개 입니다.
다시 말하면 분당 2000개 일간 2,000,000개의 데이터가 insert되며
날자가 바뀌는 새벽 00시 00분 00초에 일간 데이터 2.000,000개 (일주일전 데이터)를
delete합니다.
[결론] 제가 알기로는 인덱스는 자동으로 갱신되는 줄로 알고 있습니다.
즉 테이블(t_1)이 있고 인덱스(idx_t_1)가 있으면 insert, update, delete등의
변화가 생기면 index 역시 갱신된다고 생각했였는데
주위의 아는 사람에게 물어보니 주기적으로 재생성 해야 한다고 하더군요.
제가 알고 있는게 잘못되었는지...
문제는 index 재생성시 시간이 60초 이상을 소요하여 60초마다 데이터를 insert하는
작업에도 문제가 발생합니다.
그럼 답변을 기다리며 이만....
답변
날짜별 이력 테이블 같은데요.
조회 조건은 기간으로 Range 검색등을 하시는것 같구요.
날짜의 경우 insert 시 증가하는 방향으로 이루어지기 때문에 인덱스 중간에 빈공간이 있어도 빈공간이 재활용되기는 힘듭니다. 따라서 죽은 나무처럼 공간만 차지하고 있을 수 있습니다.
(빈공간은 update나 delete 로 발생하겠지요.)
한번 아래와 같이 빈공간이 얼마나 되는지 체크를 해보시기 바랍니다.
(아래 analyze명령어는 통계정보를 생성하는 것이 아니라 인덱스 구조를 검증하게 됩니다. 또한 아래 명령어는 Lock을 걸게 되므로 트랜잭션이 없는 시간대에 실행하십시오.)
analyze index emp_PK validate structure;
위의 명령어를 실행하면 INDEX_STATS라는 가상테이블이 생깁니다. 만약 다른 인덱스에 대해서 위에 validate structure를 실행하면 INDEX_STATS의 내용은 지워지고 새로운 인덱스의 정보가 들어갑니다. 즉, validate structure 할 때 마다 해당 인덱스에 대해서 생성되는 임시성 테이블이라고 보십시오.
아래 SQL로 얼마나 빈 공간이 있는지 체크 바랍니다.
select name, btree_space, used_space, pct_used
from index_stats;
PCT_USED가 사용된 공간의 비율입니다.
rebuild 를 하고 나면 빈 공간이 없어지고 다시 compact 해지겠죠. 다시 update와 delete를 반복하면 Index 사이즈가 커지면서 중간에 듬성듬성 빈 죽은 공간들이 나타날 것이구요.
마지막으로 위와 같은 경우 가능하다면 주기적인 Index rebuild보다는 Partition 이 가능한지 알아보시고 Range partition등을 고려해 보십시오. 안쓰는 오래된 날짜 데이터는 delete 하기 보다는 해당 파티션을 truncate 하거나 drop 하십시오.
(주기적으로 Rebuild했더니 부하가 증대된다고 하는 경우는... Block Split 때문입니다. 즉, 인덱스 키값 사이에 새로운 값이 끼어들 경우 빈공간이 없으면 Block 을 쪼개서 빈공간을 만들고 끼어들어가는 부하...)