오라클 인덱스관리

인덱스를 생성하면 테이블 select 속도가 빨라지는 것은 사실이나

용도에 맞게 인덱스를 생성해야한다.

그렇지 않으면 Full Table Scan 보다 느려지는 경우가 종종있다.

(추가로 인덱스도 tablespace 용량을 꽤나 잡아먹는다.)

 

1) DML 쿼리문 주의 사항

▶ insert 작업을 할 경우에 index split 현상이 발생할 수 있다.

index split : 인덱스의 Block들이 하나에서 두 개로 나눠지는 현상

기존 블록에 여유공간이 없는 상황에서 새로운 데이터가 들어오면 기존 블록의 내용 중 일부를 새 블록에 기록한 후

기존 블록에 빈 공간을 만들어서 새로운 데이터를 추가하게 되며 속도가 느려진다.

 

▶ delete의 문제

delete를 하면 테이블의 데이터들은 지워지는데 인덱스는 지워지지 않는다.

이는 테이블의 데이터가 100만건이지만 인덱스는 200만건이 될 수도 있기 때문에 속도 저하를 일으킬 수 있다.

 

▶ update의 문제

인덱스에는 update의 개념이 없다. 테이블에 update가 일어날 경우 인덱스에서 delete가 먼저 발생 후 새로운 데이터의 insert 작업이 발생한다.

즉 update 쿼리문은 두 가지의 작업이 인덱스에 일어나게되어 다른 DML 문장들보다 부하를 더 줄 수 있다.

 

고로 인덱스는 꾸준히 관리해줘야한다.

 

1) 인덱스 조회하기

쿼리문

-- 현재 사용자가 소유한 모든 인덱스에 대한 정보를 조회
-- 인덱스의 이름, 테이블 이름, 고유성 여부, 클러스터 여부 등등
select * from user_indexes;

-- 현재 사용자가 소유한 모든 인덱스의 열에 대한 정보를 조회
-- 인덱스 이름, 테이블 이름, 열 순서, 열 이름 등의 정보
select * from user_ind_columns;

데이터베이스 전체에 생성된 내역은 DBA_INDEXES와 DBA_IND_COLUMNS 테이블을 조회하면 된다.(DBA 권한필요)

 

 

2) 사용 여부 모니터링 하기

사용안하는 인덱스는 삭제하는 것이 좋다. 모니터링으로 관리가능하다.

-- 모니터링 시작하기
alter index emp_pk monitoring usage;

-- 모니터링 중단하기
alter index emp_pk nomonitoring usage;

-- 사용 유무 확인하기
select * from v$object_usage;

 

3) 인덱스 Rebuild 하기

인덱스는 데이터의 insert, delete, update 등을 통하여 망가질 수 있기때문에 확인하여 리빌딩 해줘야한다.

 

인덱스 밸런싱 확인쿼리

-- emp_pk 는 인덱스 이름
analyze index {emp_pk} validate structure;

select (del_lf_rows_len / lf_rows_len) * 100 BAl from index_stats;

 

해당 쿼리의 결과값이 0에 가까워야 좋은 것이다.

 

인덱스 Rebuild 작업으로 수정

alter index {emp_pk} rebuild;

analyze index {emp_pk} validate structure;

select (del_lf_rows_len / lf_rows_len) * 100 BAl from index_stats;

 

 

 

결론:

인덱스를 사용할 때는 테이블 데이터에 변화가 있을 경우 신경써줘야하며

테이블이 이유없이 느려진다 싶을 경우 1,2,3 번을 확인하여 필요에 맞는 작업을 한 번 해줄 필요가 있다.