Determine If an Index Needs to Be Rebuilt

Posted on 2008-11-10
Last Modified: 2013-12-19

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?


Question by:michael4606
    LVL 16

    Accepted Solution

    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:
    LVL 16

    Expert Comment

    ....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.
    LVL 23

    Assisted Solution

    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://
    LVL 16

    Assisted Solution

    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.
    LVL 73

    Assisted Solution

    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.

    Author Closing Comment


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


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now