Oracle Scratchpad

May 14, 2008

Index Efficiency

Filed under: Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 9:30 pm BST May 14,2008

I was looking for an old posting on the Oracle newsgroup recently, when I found a set of posts that talked about measuring the effectiveness of rebuilding some indexes.

I had contributed by pointing to my website where I have a script that demonstrates how to get some detailed information about how well the space in an index is used, and Joel Garry had responded with some results from a production instance.

The full thread starts at this URL

But for a quick summary of the conversation I had with Joel, you could go to each of these posts in turn:

 For other notes I’ve written about index rebuilds:


  1. I was looking for this!

    Thant you very much.

    Comment by lascoltodelvenerdi — May 15, 2008 @ 12:32 pm BST May 15,2008 | Reply

  2. Still much appreciated. I’ll be importing this data into new hardware/app version/O10g in a month and a half, but I don’t expect the app will do anything different there regarding these indices. In case anyone wondered, I haven’t rebuilt them, they’ll be imp’d soon enough, and I’ll have a lot more space anyways (uh oh, sounds like famous last words).

    Comment by joel garry — May 15, 2008 @ 9:09 pm BST May 15,2008 | Reply

  3. Hello Jonathan,

    What about the following calculation.
    Sum the average column length of all columns that make up the index.
    Add 10 for the rowid to this summed column length.
    Multiply this with a factor to account for overhead (I have taken 1.3).
    Multiply this by the number of rows from dba_indexes this would give us the number of bytes needed for the leaf blocks.
    Compare this to the real number of leaf blocks.

    This does not account for the fact that columns can have null values in concatenated indexes.

    Regards Hans-Peter

    Comment by Hans-Peter Sloot — May 22, 2008 @ 8:31 am BST May 22,2008 | Reply

  4. Hans-Peter,

    It looks like we’ve had similar thoughts in the past. There is a second post on my website that describes that strategy and supplies some SQL for simple B-tree indexes.

    Comment by Jonathan Lewis — May 22, 2008 @ 10:08 am BST May 22,2008 | Reply

    • Hans-Peter,
      I’ve recently made use of the script that I’ve referenced above – and realised that it has two significant errors. I’ve pointed them out on the original posting, and plan to write up a more useful example on my blog some time in the next few weeks.

      Comment by Jonathan Lewis — April 25, 2009 @ 11:21 am BST Apr 25,2009 | Reply

  5. […] Index Rebuild 10g […]

    Pingback by Index analysis « Oracle Scratchpad — September 29, 2008 @ 11:58 am BST Sep 29,2008 | Reply

  6. […] Troubleshooting — Jonathan Lewis @ 3:39 pm UTC Feb 28,2010 Some time ago I wrote a note on index efficiency that referenced, in one of the comments, an article on my old website about how you could use […]

    Pingback by Index Efficiency 2 « Oracle Scratchpad — February 28, 2010 @ 3:40 pm GMT Feb 28,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: