• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 795
  • Last Modified:

Determine If an Index Needs to Be Rebuilt


In reading various information on the web there seems to be a great debate over rebuilding standard Oracle indexes( how often and how affective it is etc.).  I have a table that experiences a large number of deletions plus others with not so heavy deletion rates.   I am suspecting that performance may be enhanced if the index is rebuilt but I'd like to be able to determine this a head of time (some systems cannot use ONLINE option and need a maintenance window etc.).

1. How can I determine if an index needs to be rebuilt, i.e. is there a formula that shows efficiency etc.?

2. Can you recommend SQL that can be run to determine indexes that need to be rebuilt?


4 Solutions
It shouldn't be necessary to ever re-build btree indexes, bitmaps maybe.

Have a look at what Tom Kyte (Oracle Guru) has to say on the matter:

....sorry, thought this would link you straight through to a web page regarding rebuilding issues, but it simply displays the search page.  Enter "rebuilding indexes" and choose one of the returned discussions.  "Rebuilding Indexes" is a good one.
DavidSenior Oracle Database AdministratorCommented:
One common rule of thumb:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
Keeping volitile indexes analyzed will also keep performance out to date.
There are many EE, Ask-Tom, and Metalink articles -- and to determine "what's best" honestly depends upon your personal environment.  My suggestions, and those of other experts, may or may not solve your site problems.  That's one reason the tuning guru's have embraced wait-based events for performance tuning.  We can track what's consuming the best time and tweak those site-specific problems.
Try Metalink 182699.1 for a dynamic script; for more help on tuning be comfortable with the explain plan utility at ttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9010.htm#SQLRF01601.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Before you start wasting time rebuilding indexes you need to determine if the index(es) are actually causing a performance problem.  You could use stats pack and monitor the performance of queries of a period of time to determine if they are getting slower.
Here's an excellent presentation exposing many of those myths as well as showing how to prove it to yourself and explaining the special cases when a rebuild "should" be done.

michael4606Author Commented:

All really great material that cover this topic in complete detail.


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now