Thursday, August 2, 2012

Index Rebuild Tips...why???

This is an answer to an email I thought I'd share...the question is doing index reorgs to fix poor clustering factor...

partial email: "Dear Dan
I very much enjoyed your explain plan/indexing seminar earlier this year.

I am now in a situation where I need to apply some of this for real. I have run your INDEX_INFO.sql and found that half of our indexes are 'Fair' or even 'Poor'. On another slide, you recommend regular Index Rebuilds, even weekly in case of 'lots of DML'.

There are many places on the internet where Index Rebuilding is called a myth. They say it does not improve the clustering factor. Our DBAs chime in on that.

Can you point me to an final answer?
"

As I discussed...clustering factor is not a 'fixable' thing...it is the relationship between the leaf blocks and how many table blocks they point to.  It is TOTALLY dependent on the order of the table and the index key...so...you rebuild the table so it is in a certain order...you will fix the intended index but what about the other indexes on that table?  IF one or two indexes had a good CF...they won't now!  So...CF is strictly the order of the table vs the indexing key...no myth...just the way it works.


...by the way...the Index_Info.sql script is yours for asking :)  It shows the clustering factor...which Oracle uses to help decide to use this index on a 'range scan' execution plan operation...download it today from www.DanHotka.com .

Dan Says:
I STRONGLY push index rebuilds to fix the index block splits on DML...using a low PCTFREE loads the index leaf blocks up...so...when you RANGE SCAN operations on them...you do fewer physical I/O.  No myth here...you do fewer physical I/O...and things run a bit faster...you rebuild indexes because you WANT to use a low pct free so you do fewer physical I/O operations when using the index...and you rebuild because with a low PCTFREE...you will get a lot of leaf block splits (when there isn't room for the entry...Oracle splits the leaf pointers across 2 leaf blocks...giving you 2 half full leaf blocks...), which causes more physical I/O when range scanning...more DML...more splits...so...you do reorgs to keep performance at its peak.

This is no myth :)  Doing fewer physical I/O operations improves SQL performance.  Hope this helps.