Forum Stats

  • 3,782,589 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

Rebuild indexes?

673946
673946 Member Posts: 1
edited Dec 4, 2008 2:21PM in General Database Discussions
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
Tagged:

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    any input regarding how often indexes should be rebuilt?
    Never. Unless there's a specific reason.
  • 641067
    641067 Member Posts: 75
    edited Dec 4, 2008 10:55AM
    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
    641067 Member Posts: 75
    edited Dec 4, 2008 11:34AM
    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
    SomeoneElse Member Posts: 14,866 Silver Crown
    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
    247514 Member Posts: 10,875 Bronze Trophy
    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
    Niall Litchfield Member Posts: 976 Bronze Badge
    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
    469753 Member Posts: 208
    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.
This discussion has been closed.