Re: When should one rebuild an index?

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 http://www.psoug.org/reference/undocumented.html#uosl so I am assuming there is a reason for this.

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".

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#CACBDJIH

Kind regards

        robert Received on Mon Dec 29 2008 - 07:14:45 CST