How to determine index rebuild?

How does one determine when it´s time to rebuild a index?
Is it the clustering factor, leaf blocks or distinct keys that indicates this? Perhaps all of them?

/Tuve

I believe its fragmentation on the leafs that determine this, which you can check in index_stats, after VALIDATE INXDEX command. There should be no more than 10 per cent of deleted rows to existing rows



-

Well, perhaps that works but there must be a simpler way to determine this. If you have thousands of indexes you cant analyze xxx validate + INDEX_STATS on every single on of them. (well, you CAN technically but it´ll take forever).
Anybody got a neat script?

/Tuve

If there have been a bulk deletes from a table, then it's necessary to rebuild the indexes.

tuvham why it would take forever?
Unfortunately as far as I know this is the only way!

Pando,
I think he is assuming you MANUALLY have to validate each index and then check the stats on them.

I haven't tried it, but you could just write a script to validate and check each index and spool to a file or something.

- Magnus

yea just use a dynamic sql script

I´m just thinking of the situation in which you are out of office, on a customer database, and don´t have your premade script to help you. Then you have to make a script to see the indexes that need rebuilding and then you have to run it, which probably takes some time (haven´t tried it but i can imagine). All this only for checking indexes. (think sga,dd,db buffer,tables,sql,parameters and so on)
I remember someone saying that its time to rebuild indexes if the index is bigger than the table, but that is probably a pretty inprecise method.
Maybe the best solution to get rid of the administration effort is to have them all rebuilt automatically on weekends.
Or what do you think?
/Tuve

Send mail to marist89@excite.com with the subject heading of "SCRIPT: rebuild_candidates.sql" and I'll send you a package that I wrote to gather these statistics...

Hi Jeff,

If you publish this under "Oracle Scripts", it will benefit everyone.

Baliga

Found it on metalink (hope you can read it):

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.