Determine If an Index Needs to Be Rebuilt

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
michael4606Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MilleniumaireCommented:
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::
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MilleniumaireCommented:
....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.
0
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.
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MilleniumaireCommented:
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.
0
sdstuberCommented:
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
0
michael4606Author Commented:
Guys,

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

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.