index rebuild 작업

질문 :

index에 관해서 공부를 하다가 궁금한 점이 있어 이렇게 질문을 등록합니다.

테이블에 데이터가 자주 들락날락해서 delete된 데이터의 index가 그대로 남아있어, 필요이상으로 많은 공간을 차지할 때 한다는 것을 알고 있습니다.

그런데, 이렇게 필요이상으로 index가 공간을 많이 차지한다는 것을 어떻게 알 수 있을까요?

---

답변1 :

아래에 있는 두 방법의 결과로 판단합니다.


첫번째 방법 :

우선 분석하고자 하는 인덱스에 대한 통계정보를 생성합니다.

SQL> analyze index 인덱스명 compute statistics;
-- 수백만건 이상의 row를 지닌 테이블에 대한 인덱스인 경우 compute statistics 대신에 estimate 옵션을 사용할 것.

아래 쿼리 결과의 blevel값이 4가 넘으면 index rebuild를 하는 것이 좋습니다.
이 blevel(Branch level)이 의미하는 것은 오라클이 index access를 할 때 몇 단계를 거쳐서 블럭의 위치를 찾아가는 가와 관계가 있습니다.

SQL> select index_name, blevel
     from user_indexes
     where index_name = '인덱스명';

두번째 방법 :

아래의 쿼리를 이용하여 index_stats테이블에 추가적인 인덱스 정보를 생성합니다.

SQL> analyze index 인덱스명 validate structure;

아래 쿼리 결과의 pct_deleted가 20%이상으로 나타나면 rebuild 대상입니다.

그리고 distinctiveness는 인덱스가 만들어진 컬럼의 값이 얼마나 자주 반복되는지를 보여주는 값입니다.

예를 들면, 만일 1만건의 row와 9000건의 서로 다른 값을 가진 테이블이 있을 때 distinctiveness값은 다음과 같이 계산됩니다.

(10000-9000)*100/10000 = 10        => 컬럼의 값이 잘 분산되어 있음

또 다른 예를 들면, 1만건의 row가 있지만 2가지 값으로만 중복되어 있다면 distintiveness값은

(10000-2)*100/10000 = 99.98        => rebuild 대상이 아니라 bitmap index로 만들 대상(99%이상이면 bitmap index 대상)

SQL> select del_lf_rows*100/decode(lf_rows,0,1,lf_rows) pct_deleted,
            (lf_rows-distinct_keys)*100/decode(lf_rows,0,1,lf_rows) distinctiveness
     from index_stats
     where name = '&index_name';

---

답변2 :

답변2도 답변 1과 같은 내용이지만 스크립트를 사용함으로써, 한번에 여러개의 rebuild 대상 index를 찾을 수 있다는 장점이 있다.


결과 정보를 휘발성으로 저장하는데, 그 결과를 통해서 rebuild 대상을 체크할 수 있음.
주의할 점은 아래의 쿼리 실행시 Lock이 발생하므로, 업무시간 이외에 할 것을 강추함.

SQL> analyze index index_name validate structure ;

rebuild_indx.sql

REM =============================================================
REM
REM rebuild_indx.sql
REM
REM Copyright (c) Oracle Software, 1998 - 2000
REM
REM Author : Jurgen Schelfhout
REM
REM The sample program in this article is provided for educational
REM purposes only and is NOT supported by Oracle Support Services.
REM It has been tested internally, however, and works as documented.
REM We do not guarantee that it will work for you, so be sure to test
REM it in your environment before relying on it.
REM
REM This script will analyze all the indexes for a given schema
REM or for a subset of schema's. After this the dynamic view
REM index_stats is consulted to see if an index is a good
REM candidate for a rebuild or for a bitmap index.
REM
REM Database Version : 7.3.X and above.
REM
REM =============================================================

prompt
ACCEPT spoolfile CHAR prompt 'Output-file : ';
ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
prompt
prompt
prompt Rebuild the index when :
prompt - deleted entries represent 20% or more of the current entries
prompt - the index depth is more then 4 levels.
prompt Possible candidate for bitmap index :
prompt - when distinctiveness is more than 99%
prompt
spool &spoolfile

set serveroutput on
set verify off
declare
c_name INTEGER;
ignore INTEGER;
height index_stats.height%TYPE := 0;
lf_rows index_stats.lf_rows%TYPE := 0;
del_lf_rows index_stats.del_lf_rows%TYPE := 0;
distinct_keys index_stats.distinct_keys%TYPE := 0;
cursor c_indx is
select owner, table_name, index_name
from dba_indexes
where owner like upper('&schema')
and owner not in ('SYS','SYSTEM');
begin
dbms_output.enable (1000000);
dbms_output.put_line ('Owner Index Name % Deleted Entries Blevel Distinctiveness');
dbms_output.put_line ('--------------- --------------------------------------- ----------------- ------ ---------------');

c_name := DBMS_SQL.OPEN_CURSOR;
for r_indx in c_indx loop
DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);
ignore := DBMS_SQL.EXECUTE(c_name);

select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
into height, lf_rows, del_lf_rows, distinct_keys
from index_stats;
--
-- Index is considered as candidate for rebuild when :
-- - when deleted entries represent 20% or more of the current entries
-- - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
-- Index is (possible) candidate for a bitmap index when :
-- - distinctiveness is more than 99%
--
if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
end if;

end loop;
DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/

spool off
set verify on
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

샘플 결과

Sample Output
-------------

Rebuild the index when:

- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.

Possible candidate for bitmap index:

- when distinctiveness is more than 99%

Owner Index Name % Deleted Entries Blevel Distinctivenes
--------------- ------------------------- ----------------- ------ -------------
SMS PLATMAP_I1 31.504 1 32.927
SMS PLATMAP_I2 27.682 1 29.399
SMS PLATMAP_I3 31.237 1 31.237
SMS PRODMAP_I4 8.765 5 99.9
SMS SB_CR_BOM_ITEMS_I1 34.496 2 97.356
SMS SB_CR_OS_VERSIONS_I1 51.942 1 68.063
SMS SB_CR_RELEASES_I1 34.584 1 18.426
SMS TAR_HEAD_I2 21.728 5 22.344

PL/SQL procedure successfully completed.


참조 : http://cafe.naver.com/prodba/16759
         http://oracleclub.com/article/46676