Re: When should one rebuild an index?

From: DA Morgan <>
Date: Mon, 29 Dec 2008 07:49:21 -0800
Message-ID: <>

Robert Klemme wrote:
> On 28.12.2008 22:50, DA Morgan wrote:

>> Now lets try it my way. I rebuild and repopulate that table.
>> SQL> SELECT rows_per_block, count(*) blocks
>>   2  FROM (
>>   3    SELECT /*+ cursor_sharing_exact
>>   4               dynamic_sampling(0)
>>   5               no_monitoring
>>   6               no_expand
>>   7               index_ffs(test,ix_test)
>>   8               noparallel_index(test,ix_test)
>>   9           */
>>  10      sys_op_lbid(90508, 'L', test.rowid) block_id,
>>  11      COUNT(*) rows_per_block
>>  12    FROM test
>>  13    WHERE object_name IS NOT NULL
>>  14    GROUP BY sys_op_lbid(90508, 'L', test.rowid))
>>  15  GROUP BY rows_per_block;

> Daniel, is there any particular reason why you did not include any
> sorting (presumably on ROWS_PER_BLOCK ASC or maybe on BLOCKS DESC) here?
> It seems to me that the result is then easier to digest. You do not
> have it on so I am
> assuming there is a reason for this.

No good reason and you are absolutely correct that sorting would help. Also helpful would be buckets with counts so one could quickly grasp the number of blocks in one container as compared with another.

If I get some free time I may rewrite though if anyone else would like to I will be happy to post their work and their name.

> Unfortunately I don't have an Oracle database at hand right now so I
> cannot experiment myself. However, I wonder whether there is a way
> using documented features to get at the number of index entries per
> block. Using a query on DBA_EXTENDS it should be possible to generate a
> list of all (FILE_ID, BLOCK_ID)s of a database object. Now we would
> only need a function that would return the number of entries given

> If one could generate a list of all ROWIDs of all index entries (which I
> doubt is possible because index blocks look very different from data
> blocks) one could use functions in DBMS_ROWID to extract block addresses
> and use that for grouping.
> An alternative might be to use DBMS_SPACE.SPACE_USAGE to get at least a
> rough idea how evenly rows are distributed across index blocks and
> DBMS_SPACE.UNUSED_SPACE to see how much space is "wasted".

One quick solution is to use DBMS_SPACE.CREATE_INDEX_COST to get a good idea of how big an index should be: Then compare it with the actual index.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Mon Dec 29 2008 - 09:49:21 CST

Original text of this message