chandubtech

 Profile Answers by chandubtech Questions by chandubtech

• Aug 2nd, 2008

 

Actually you need to rebuild the indexes for 2 main scenarios.
1)deleted entries represents 20% or more of the current entries
2)the index depth is more than 4 levels

Oracle index nodes are not deleted physically when the rows of tables deleted nor the entries are removed from the index.Oracle deletes these index nodes logically and leaves 'dead nodes' in the index tree itself where these deade nodes may be reused if another adjacent entry is required.

how ever when large number of table rows are deleted,there may be chance of deleted leaf nodes will make index fast full scans for a longer period.
Deleted leaf rows:the term deleted leaf rows referred to the number of  index inodes that have been deleted logically by oracle when rows of table deleted.

in oracle you must use these commands;
SQL>analyze index index_name compute statistics;
SQL>analyze index index_name validate structure;

SQL>select  del_lf_rows/decode(lf_rows,0,1lf_rows) from index_stats where name='index_name';

if the query returns more than 20 %.then you may think of rebuilding the indexes.

or in other way :
after you analyze the above 2 statements ,you may think of rebuild the indexes where height is more than three levels
Index height:The number of levels that index would span when massive inserts happened to tables
Gets Per index access:the number of logical i/o 's required when a row with an index is fetched.
also we need to think of rebuild the indexes when gets per index access is more than 5

example when to rebuild the indexes:
SQL>create table temp_stats  as select name,most_repeated_key,distinct_keys,del_lf_rows,height,blk_gets_per_access from index_stats;

SQL> insert into temp_stats (select name ,most_repeated_key ,distinct_keys ,del_lf_rows ,height ,blks_gets_per_access  from index_stats);

SQL>select distinct name c1,most_repeated_key c2,distinct_keys c3,del_lf_Rows c4,height c5,blks_gets_per_access c6 from temp_stats where height > 3 or del_lf_rows > 10 order by name;

after analyzing the indexes ,if the above query returns any rows then you need to rebuild the indexes.

 

SQL>alter index index_name rebuild tablespace tablespace_name storage (initial new_initial next new_next freelists new_freelist_number )

 

Though the lengthy  but content one