JL Computer Consultancy

Index Efficiency

Aug 2004

Updated Oct 2007

There are endless arguments on the internet about how often you should rebuild your indexes, and how much benefit you might get from doing index so. I don’t plan to address the question of how you work out the cost/risk/benefit equation for your site, but you may be interested in the following script because it does far better than anything else on the internet at identifying indexes that might (note the emphasis) benefit from a rebuild.

The script works only for version 9i and above, and depends on the undocumented function sys_op_lbid() which can be used to acquire the information in index leaf blocks directly. 

In this case, we start by scanning an index segment as efficiently as possible and working out how many entries there are in each block. By using an in-line view we take this intermediate result one step further and re-aggregate the result to produce a report showing how many blocks have “N” entries.

As an example of the output, consider the following:

Back to Index of Topics



-------------- ----------

            24          1

            66          1

            68          4

            69          5

            70          4

            71          5

            72          4

            73          5

            74          4

            75          4

            76          5

            77          5

            78          2

            79          3

            80          2

            82          1

-------------- ----------

Sum                    55

In this example, most of the leaf blocks have about 75 rows, plus or minus a few, and there is just one extreme block (which we can safely assume is the last block of the index) that is half empty.  If you have recent statistics for the index, you can compare the total number of blocks in the report with the statistic leaf_blocks from the user_indexes and can check (roughly) how many blocks below the high water mark are empty - some of the difference will be due to branch blocks.

Note: if you don’t have recent statistics, then the dbms_space package gives you a cheap way of finding out how many blocks there are below the high water mark.

Update Oct 2007:

A recent discussion on the comp.databases.oracle.server newsgroup drew my attention to this page, and reading it made me realize there is an error in the comment about recent statistics and leaf_block counts.

Here are a couple of possible variations on the content of the report that may give you some hints about the need to rebuild or, perhaps more appropriately, coalesce, the index.

1)            If you can see that a large number of blocks have a very small number of rows_per_block, whereas a small number of blocks have a much larger number of rows_per_block, this may be an example of a degenerate index being used to handle a FIFO (first in, first out) queue – it could be a candidate for a coalesce.

2)            If you see that a relatively small number of blocks have far more rows – in the order of double – than the rest of the index, it is possible that you are suffering from an index that is splitting badly and not refilling in the middle. It is possible that a rebuild may pack your index into half the space and not lead to lots of splitting immediately afterwards. (It may be that within hours of the rebuild, every block has split again, in which case the rebuild was probably a mistake).

In any case, you should probably take a little time to think about indexes where the pattern is NOT “mostly the same usage but a few blocks with significantly less usage”. Of course, there are always exceptions – the last bit of advice will miss the extreme case where “most of the index has gone bad, but bits of it are a disaster”, but I’ll tell you how to spot that case in another article.

Update April 2009:

For a practical example of using this code, there is a case-study on my blog; and there’s a friendlier version of the code, on my blog here.

Back to Index of Topics


rem     Script:        index_efficiency.sql

rem     Author:        Jonathan Lewis

rem     Dated:         Sept 2003

rem     Purpose:       Example of how to check leaf block packing

rem     Notes
rem     Last tested
rem     Example of analyzing index entries per leaf block.

rem     The code examines index T1_I1 on table T1.

rem     The index is on (v1, small_pad). Both columns appear

rem     the where clause with a not null test to avoid issues

rem     relating to indexes with completely nullable entries.


rem     For a simple b-tree index, the first parameter to the

rem     sys_op_lbid() function has to be the object_id of the

rem     index.


rem     The query will work with a sample clause


rem     Check that the execution path is an index fast full scan


column ind_id new_value m_ind_id



        object_id ind_id




        object_name = 'T1_I1'



break on report skip 1

compute sum of blocks on report




        count(*) blocks

from (










               sys_op_lbid( &m_ind_id ,'L',t1.rowid) as block_id,

               count(*)                              as rows_per_block



        --      t1 sample block (100)


               v1 is not null

        or      small_pad is not null

        group by

               sys_op_lbid( &m_ind_id ,'L',t1.rowid)


group by rows_per_block

order by rows_per_block



Back to Index of Topics