I've heard that rebuilding an index can in some cases cause queries using this index to run slower and do more buffer gets (after the rebuild, not during). Is this true?

A test case showing this will be greatly appreciated.

actually u have to rebuild index on following conditions.

select height from index_stats ;

if height > 20

u have to rebuid the index.

select (del_lf_rows/lf_rows)*100 from index_stats ;

if (del_lf_rows/lf_rows)*100 > 20% the rebuild the index;

during index rebuild u can reclaim space and improve performance by eliminating fragmentation and reducing the blocks used.

anyone please check what i said is right or not.

I would strongly suggest spending some time reading Richard Foote's presentation on index internals "Rebuilding the Truth"

http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf

The metrics you've posted are not particularly accurate. There are very few cases where b-tree indexes need to be rebuilt, none of which are captured by these criteria.

As for the original poster, yes, rebuilding indexes can force Oracle to do additional work in the days after the rebuild as it does a variety of block splits in order to get back to the "steady state" configuration.

Justin

I was merely pointing out a danger in running the "index rebuild" command especially when there are other users logged in and accessing the table and index eing rebuild. I am aware there are other conditions that constitute a rebuild.

Hoisted by my own petard!

I'm the one who wrote the anecdote. Management is very happy with the results of the rebuild and wants me to rebuild indexes automatically on regular basis, to prevent the next such problem.

I'm a bit worried about the rebuild because I'm not sure we fully acknowledge the problems it may cause in the system. After all, the fact that something helps when there is a problem doesn't mean it can't cause problems in a healthy system. We don't take anti-headache medicine every day to prevent headaches.

Thank you, and I'll appreciate any opinions regarding regular index rebuilds.

Lesson learned , recognize people by there name not by there blog name


1) If nothing is broken don't fix it , but be proactive i.e don't wait till things break
2) Test case work on Ideal Scenario, but not always work on production

As per manufacturer/car expert, my car suppose to give mileage of 16 Km/Liter , but it gives only 10-12 km/liter, because it depend on Road condition , Traffic condition and of course driver.

We use to rebuild index regular basis on criteria of, deleted entries represent 30 % or depth go above 4. There are only few indexes, which have depth of 4 , all others indexes are below that. To get this information, we need to run ANALYZE ... VALIDATE STRUCTURE , that look too recourse consuming. So, we run ANALYZE ... VALIDATE STRUCTURE command on test environment immediate after refreshing from production and before running data masking script. After ANALYZE , we get list of Indexes which are candidate for index rebuild. Mostly we get very few indexes and those indexes are on tables which are heart and soul of application .

We rebuild indexes online on off-peak time For example Saturday 7-8:00 PM PST, with no logging. After rebuild we collect stats on tables(for which we rebuild indexes) with cascade=true and make indexes logging.

I wonder, We collect stats same way regularly, if index rebuild is myth and above criteria not work well, then why execution plan changed after few weeks and after index rebuild, why good execution plan come into picture.

Not sure , how many Database with very high volume of DML exists that never need index rebuild !!!!!!

If you have an index with a height greater than 20, you certainly might want to rebuild it once - but you would certainly want to make sure that this was necessary before you did so, and you would probably want to find out why it had got to such a state.

For an indication on how indexes can degenerate, you might like to read this (not entirely serious) note on my website:

http://www.jlcomp.demon.co.uk/22_how_high.html

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

Thank you Jonathan!

ORA-01410 is indeed a risk we did not think of at all.

We did take into account the fact that the index will actually take more space eventually. Now that I think of it - taking more space may mean that range scans will read more blocks and therefore take more time than before... In addition to the fact that having different execution plans for key queries every month will not be fun at all.

Thanks again for the useful information.