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

안녕하세요.

앞서 인덱스의 개념과 종류에 대해 알아보았습니다.

[개발/DB] - [오라클] 인덱스의 원리 및 개념과 종류(B-TREE, BITMAP INDEX)

오늘은 인덱스의 주의사항과 실습을 통하여 인덱스의 관리법을 알아보고자 합니다.

인덱스의 주의사항

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

용도에 맞게 인덱스를 생성하여 사용해야 합니다.

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

1)  DML 쿼리문 주의 사항

 ○ insert 작업을 할 경우에 INDEX Split 현상이 발생할 수 있습니다. 

   → INDEX Split로 인해 속도가 느려질 수 있습니다.

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

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

       ※ INDEX Split : 인덱스의 Block들이 하나에서 두 개로 나누어지는 현상

 ○ delete의 문제

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

      이는 테이블의 데이터가 5만건이지만 인덱스는 10만건이 될 수도 있기 때문에 속도의 저하를 가지고올 수 있습니다.

 ○ update의 문제

   → 인덱스에는 update의 개념이 없습니다. 테이블에 upate가 일어날 경우에는 인덱스에서 delete가 먼저 발생한 후

      새로운 데이터의 insert 작업이 발생 합니다. 즉 update의 쿼리문은 두가지의 작업이 인덱스에 일어나게 되어 

      다른 DML 문장들 보다 부하를 더 줄 수 있습니다.

2)  다른 SQL문에 끼치는 영향

 ○ 옵티마이져가 실행 계획을 세우는데 이에대해 최근에 생성된 인덱스가 영향을 줄 수 있습니다.

인덱스 관리 방법

1) 인덱스 조회하기

쿼리문

select * from user_indexes;

select * from user_ind_columns;

select * from user_indexes; 에 대한 결과 입니다.

데이터베이스 전체에 생성된 내역은 DBA_INDEXES와 DBA_IND_COLUMNS 테이블을 조회하면 됩니다.

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

 ○ 사용안하는 인덱스는 삭제하는 것이 맞으므로 모니터링하여 관리할 필요가 있습니다.

 ○ 모니터링 시작하기

alter index emp_pk monitoring usage;

 ○ 모니터링 중단하기

alter index emp_pk nomonitoring usage;

 ○ 사용 유무 확인하기

select * from v$object_usage;

INDEX Rebuild 하기

인덱스는 한번 만들어 놓으면 영원히 잘 작동하는 것이 아니라

데이터의 insert, delete, update 등을 통하여 망가지기도 하는데 

지속적으로 잘 사용하기 위해서는 꾸준히 관리해줘야 합니다.

먼저 저의 테이블에 데이터를 생성하였습니다.

begin 

for i in 1..1000 loop

insert into emp (emp_no, user_nm) values(i, 'test'||i);

end loop;

commit;

end;

/

이제 데이터 400건을 지우겠습니다.

begin 

for i in 500..900 loop

delete from emp where emp_no=i;

end loop;

commit;

end;

/

인덱스 밸런싱 확인 쿼리

analyze index emp_pk validate structure;

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

결과

결과 값으로 0에 가까워야 좋은 것 입니다.

현재 40%의 인덱스 밸런싱이 망가진 상태 입니다.

이전에 약 400건의 데이터를 지운 결과로 추정됩니다.

이에 인덱스 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;

결과

결과값이 0으로 수정된 것을 확인 할 수 있습니다.