When to Rebuild Index 2

[engineer2100]

Question is simple..

Under what circumstance should we start thinking of rebuilding the index?

i have seen people talking about Index clustering factor, height of the b-tree within a B*Tree index etc but confused on what these could mean and how they actually affect the rebuilding the index!!

Thanks
Engi.

 

[taupirho]

My answer may seem trite but I would only rebuild an index when I need to rebuild an index. You create an index for one reason only - performance. If performance is suffering and you have ruled out any other obvious reasons then you might start to look at your indexes.

Interested to hear others opinions.

 

[Scunningham99]

# WE TEND TO REBUILD INDEXES THAT HAVE WASTAGE OVER 20%

sql> analyze index custom.index01 validate structure; (or we can use dbms_stats)

Then Query INDEX_STATS

sql> select * from INDEX_STATS; --This is a session specific view for the index last analyzed.

lf_rows --number of values currently in index.
lf_rows_len --sum of all the length of values in bytes
del_lf_rows --number of values deleated rom the index
del_lf_rows_len --length of all deleated rows.



sql> select name, (del_lf_rows_len/lf_rows_len) * 100 "Wastage" from INDEX_STATS;

--If the above query is above 20% then we need to rebuild with:-

SQL> alter index index01 REBUILD;


***************************

NOTE:

WHEN REBUILDING INDEXES, WE MUST GATHER THE STATS AGAIN AFTERWARDS! OTHERWISE THE STATS ARE LOST


Sy UK

 

[engineer2100]

Thanks Scunningham!

 

[jimirvine]

Read this thread

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730

It is excellent

 

[engineer2100]

thanks Jim.

now i have a question which was not very obvious from Tom's reply (dumb me!)

Why does a rebuild do the following?

o the system would generate 4.5 times the redo
o the system would run slower
o the system would consume more resources (CPU, IO, latching, etc)
o the system would not be able to handle the same user load

 

[jimirvine]

You missed the Sentence prior to the list. i.e.

In this case, rebuilding the index on their system had these effects:

Rebuilding an index on a transactional system is very likely to cause many block splits. a block split happens when there is no space in an index block for an index entry. This requires a new block to be added to the leaf level, new entries added to at least 1 other level (e.g. a root or branch level) in extreme cases, block splits on branch levels or even additions of extra branch levels. All of which causes redo. Redo causes the redo buffer to fill, which causes lgwr to write which can cause dbw0 to write. log switches can occur which may cause arc0 processes to start writing, requests for extra index blocks require dml on the data dictionary, which acquires locks and latches all of which (and more) increases disk contention, which reduces the effective level of concurrency of the db. This is very much a summary of what goes on, and you'd really need to do a lot of reading if you are actually interested in the machinations of this. Expert 1 on 1 (By Tom Kyte)is a good starting point

 

[engineer2100]

WOW!!!!!!! Thats some explanation!

Thanks Jim!