Mohamed Houri’s Oracle Notes

May 12, 2015

Index Efficiency

Filed under: Index — hourim @ 7:03 am

I used Jonathan Lewis script to locate degenerated indexes –-or indexes that are occupying more space than they should–. Among those indexes I have isolated this one:

16:20:33:TABLE1 - PK_TAB1
Current Leaf blocks: 2,846,555 Target size:1,585,492

According to this SQL script the above index possesses 2.8 million worth of leaf blocks while it should normally occupy half this number of blocks.

The sys_op_lb_id function when applied on this index gives the following average leaf block per index key picture:


-------------- ----------
 2              1
 7              1
 27             1
 32             1
 92             1
 94             1
 103            1
 107            1
 108            1
 111            1
 112            800
 113            1,627,529
 422            980,894
 423            40
 432            1
 434            1
 448            5496
 449            32803
 450            7
 456            3
 458            1
 466            1
 478            54
 479            200
 487            1
 ----------    -----------
sum             2,979,747

Spot that odd value of 1.6 million leaf blocks (out of a total of 2,9 million) we have to visit to get only 113 index keys. Add to this the other 980,984 leaf blocks we need to visit to get an extra 422 index keys and you might end up by approximatively having to visit the entire index leaf blocks to get only a couple of hundred of index keys. That is a completely degenerated index.

Let’s then rebuild it and check if we will get back the reclaimed space:

SQL> alter index PK_TAB1 rebuild parallel 8;

SQL> alter index PK_TAB1 noparallel;

SQL> break on report skip 1

SQL> compute sum of blocks on report

SQL> select
        count(*) blocks
        sys_op_lbid( &m_ind_id ,'L',t1.rowid) as block_id,
        count(*) as rows_per_block
        TABLE1 t1
        tab_id is not null
      group by
       sys_op_lbid( &m_ind_id ,'L',t1.rowid)
   group by rows_per_block
   order by rows_per_block
Enter value for m_ind_id: 53213
Enter value for m_ind_id: 53213

-------------- ----------
 26            1
 206           1
 208           1
 243           1
 249           1
 272           1
 316           1
 339           1
 422           1,558,800
 423           53
 432           1
 448           5496
 449           32803
 458           1
 478           54
 479           200
 487           1
sum          1,597,417

Notice the new number of index leaf block we’ve got after rebuilding the index (1,597,417) and compare it with the number predicted by Jonathan Lewis script (1,585,492). That’s really very accurate. The initial estimation is almost 100% accurate. In passing the new index size has been reduced by at factor of 46%.

While rebuilding the index has reduced drastically the number of leaf blocks and the disk space they occupy, that odd value of 1,558,800 leaf blocks we have to visit to get only 422 index keys is still present. This has prompted me to try coalescing the index even though I was not very confident that such a high number of leaf blocks could be merged with adjacent leaf blocks making the index less smashed.

SQL> alter index PK_TAB1 coalesce;

-------------- ----------
           26          1
          206          1
          208          1
          243          1
          249          1
          272          1
          316          1
          339          1
          422          1,558,800
          423          53
          432          1
          448          5496
          449          32803
          458          1
          478          54
          479          200
          487          1
sum              1,597,417

Definitely this primary key index has a strange way of being filled up which I have to figure out with the Java developers.

The bottom line of this article is that Jonathan Lewis script locating degenerated index is amazingly precise.


  1. Mohamed,

    It’s most gratifying when an example like this gets published. Thanks.

    Looking at the before and after figures I’d guess that the index has been rebuilt (at least) once in the past giving you a very large hump of 422 rows per leaf block, and since that time the index degenerated fairly rapidly into the 50% ITL and 50% wastage after ITL that happens very easily with sequential indexes subject to concurrent inserts. You probably recall my notes on the ITL explosion problem:

    Comment by Jonathan Lewis — May 12, 2015 @ 7:14 am | Reply

  2. Merci pour l’info, je ne connaissais pas, ça m’a permis de gagner quelques 100aines de Mo sur la base de mon ERP, pas négligable…

    Comment by francoisgregoirefrançois — May 12, 2015 @ 10:03 am | Reply

  3. Hi Mohamed

    I think you’re reading the data incorrectly and so some of your conclusions are incorrect. The histogram seems to be displaying how many index entries there are in the leaf blocks, not how many leaf blocks need to be visited per indexed value (else the sum of leaf blocks would not approach the total leaf blocks in the index).

    As such, it’s incorrect to state for example “that odd value of 1.6 million leaf blocks (out of a total of 2,9 million) we have to visit to get only 113 index keys”. It actually means that 1.6 million leaf blocks have just 113 index keys. However there’s nothing to suggest the distinctiveness of these keys. They might all have the same value, they might all have different values, they might be somewhere in between (although this becomes clearer after the rebuild). If say these index entries are all unique, then all these 113 index entries in these blocks might all be different.

    What it does suggest is that there are a lot of leaf blocks that might not be effectively filled as so many only have just 113 index entries, when so many also have 422 index entries.

    Another theory on what might have happened here is that there was some form of delete that occurred over time that deleted a whole bunch of index entries from these leaf blocks with just 113 index entries. After the rebuild, these have been compacted again down to the more effective 422 entries per block. Larger index entries might explain why some leaf blocks can only fit a fewer number of index entries.

    Clearly the coalesce was going to have no impact straight after the rebuild and the reason why all these leaf blocks now have 422 index entries hopefully makes sense.

    One last point is that the script you use doesn’t appear to consider leaf blocks that contain no index entries. Therefore, an index might be in worse shape than shown in the listing. This can be picked up by differences between the sum of leaf blocks in the histogram and the actual number of leaf blocks in the index.

    Comment by Richard Foote — May 13, 2015 @ 1:28 am | Reply

    • Richard,

      I don’t see where Mohamed makes any comment that suggests that you need to visit N leaf blocks for a single key – the comment that you quote and explain says exactly what you say in your explanation. I think you must have missed the line where Mohamed points out that this is a primary key index – which could explain why his text makes no distinction between “index entries” and “index keys”. I think you may also have been a little misled by the heading “rows per block” in the detail list: that’s my heading for a simple srcipt to report index entries per index leaf block.

      It is unfortunate that the scripts doesn’t allow for leaf blocks that have become empty, I should have pointed that out in the comments in the scripts, and possibly reported the segment size as a further comparison. Bear in mind that that’s a defect of the code used in dbms_stats.gather_index_stats. The LEAF_BLOCK value shown for an index doesn’t include empty leaf blocks – which means the optimizer’s estimate of an index fast full scan is essentially based on the wrong figure, even though it probably doesn’t matter very often. You’ll notice, by the way, that the sum of the blocks recorded by the histogram is larger than the “Current Leaf Blocks” report from my code – which suggests that the last stats collection was an estimate, or that the index had grown since the last stats collection.

      Comment by Jonathan Lewis — May 13, 2015 @ 6:18 am | Reply

    • Richard,

      Seeing your subsequent comments on twitter I believe you’re correct. My reading of the phrase you quoted implicitly added “in each one” to the “only 113 index keys”, but Mohamed’s later comments do suggest that he has read this to mean “there is a portion of the index covering 1.6M blocks that between them contain a total of only 113 index entries”.

      Comment by Jonathan Lewis — May 13, 2015 @ 6:37 am | Reply

  4. Richard,

    Thanks for your comments. This article aims mainly to show how precise is Jonathan Lewis script to locate degenerated index.
    I rebuild a couple of other indexes signalled by this script and the precision was almost 100% correct.

    I was reading incorrectly the sys_op_lbid output and thanks so much for correcting me I highly appreciate.
    But Jonathan Lewis is absolutely correct when he say that what I wanted to emphasize is
    “there is a portion of the index covering 1.6M blocks that between them contain a total of only 113 index entries”.

    Best regards

    Comment by hourim — May 13, 2015 @ 12:29 pm | Reply

    • Mohamed,

      I appreciate that English is at least your 2nd, if not third, language, so when Richard and I interpret the same English words in two different ways it’s not surprising that we cause confusion.

      The key point is that the 1.6M leaf blocks hold 113 index entries each – for a total of about 1.8M index entries. Your comments about reading most of the index, and about coalesce, and talking the the Java developers suggest that you had read the output as meaning 1.6M blocks and a total of 113 index entries with lots of empty blocks. There appear to be about 67M rows in the index (and, since it’s the PK, 67M rows in the table).

      Comment by Jonathan Lewis — May 13, 2015 @ 12:40 pm | Reply

      • Hi Jonathan

        I think the decimal point is a little awry here 🙂 There are about 183M total index entries that lived in the 113 index entry blocks and about 675M total rows in the table by my calculations.

        Comment by Richard Foote — May 14, 2015 @ 5:10 am

      • Richard,
        Agreed – not sure how I lost all those powers of 10.

        Comment by Jonathan Lewis — May 14, 2015 @ 4:13 pm

      • Thank goodness I didn’t manage to claim that the whole index held fewer entries than that subsection on its own.

        Comment by Jonathan Lewis — May 14, 2015 @ 4:15 pm

  5. Hi Mohamed

    No worries. I get corrected all the time, one the advantages (and disadvantages I guess) of having a well read blog 🙂

    I think your quote would read better if it stated “there is a portion of the index covering 1.6M blocks that only contain 113 index entries in each and every block”. Or some such 🙂

    Comment by Richard Foote — May 14, 2015 @ 4:58 am | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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.

Blog at

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog


Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: