Determine If an Index Needs to Be Rebuilt

michael4606
michael4606 used Ask the Experts™
on
Hello,

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?


Thanks,

Michael
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:

http://asktom.oracle.com/pls/asktom/f?p=100:1:0::NO:RP::
....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.
David VanZandtOracle Database Administrator III
Commented:
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.
 
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.
Most Valuable Expert 2011
Top Expert 2012
Commented:
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.

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

Author

Commented:
Guys,

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

Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial