Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Rebuild indexes?

673946Dec 4 2008 — edited Dec 4 2008
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

Comments

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.
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 1 2009
Added on Dec 4 2008
#general-database-discussions, #index
7 comments
1,143 views