Rebuild indexes?

673946

I am working with a large scale database with approximately 100 tables where some tables grow with 100-500 million rows per year. The performance in of the system is fairly good but I was wondering if anyone has any input regarding how often indexes should be rebuilt?

Kind regards,

Sumpen

SomeoneElse

any input regarding how often indexes should be rebuilt?

Never. Unless there's a specific reason.

641067

Please ericch2,
Could you specify why is not recommended to rebuild an index?

I found a lot of info about this "discussion-topic" because there are some very diferents opinions about this issue.

Thanks in advance by your clarification.

Martin

ericch2 wrote:

any input regarding how often indexes should be rebuilt?

Never. Unless there's a specific reason.

Edited by: martinmorono on Dec 4, 2008 1:55 PM

641067

Inside my company, We are using this self-made rules to consider an index as candidate to be rebuild:

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%

But, as I'd said before it's matter of a big discussion why or when an index rebuild should be applied. Possibly, by applying this criteria you shouldn't rebuild this large indexes ever, all depends in how many operations you do over these indexes to make them "worthless" enough to become them candidates for this rebuild.

Edited by: martinmorono on Dec 4, 2008 2:31 PM

SomeoneElse

That's what I meant by "specific reason".

There are a lot of posts in these forums from people who rebuild indexes daily/weekly/monthly for no other reason than "it seems like a good idea".

247514

You might want to check this article by Jonathan Lewis

When Should You Rebuild an Index?
http://www.dbazine.com/oracle/or-articles/jlewis14

Niall Litchfield

martinmorono wrote:
Inside my company, We are using this self-made rules to consider an index as candidate to be rebuild:

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%

That'll come from the old metalink guidance. It doesn't exist now. Oracle reviewed it and pulled it. You might want to think about a 1 billion row table (2 years of the OP's highest growth rate). Would an index on a randomly distributed varchar column on that table of a height of 5 be especially inefficient?

I don't understand 'distinctiveness'

Niall Litchfield
http://www.orawin.info/

469753

Richard Foote wrote an excellent paper on this topic several years ago.
The technology and recommendations still stand.
http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf

In short - it is not often that you will need to rebuild your indexes but there are times to do so.