Re: When should one rebuild an index?

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;

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
> (FILE_ID, BLOCK_ID).

> 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. http://www.psoug.org/reference/dbms_space.html

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org