Using DBMS_SPACE.UNUSED_SPACE to Measure Index Quality, Lock Table?

Hello,

According to all sources that I can find, the best way to determine the storage quality of a v8.0.x index is by running 'ANALYZE INDEX ....' and checking the ratio of DEL_LF_ROWS to LF_ROWS in INDEX_STATS.

I am not sure whether or not 8i+ has any utilities that allow for a better way to determine index quality.

However, my understanding is that the 'ANALYZE INDEX' command locks out the table. Thus, on the systems that I use, the index storage quality can also be measured by a routine that uses the DBMS_SPACE.UNUSED_SPACE procedure to measure an index's storage quality.

The storage quality is given by the ratio of the number of blocks needed to store an ideal index (Using space per block, space per index entry, and number of table rows) to the actual number of blocks used by the index.

As far as I can tell, the calculation does not lock out any table. (Am I correct?)

Is this method (Assuming that the right parameters are passed to DBMS_SPACE.UNUSED_SPACE, etc..) almost as accurate as using INDEX_STATS?

Would a DBMS_SPACE.UNUSED_SPACE storage quality of, say, 60% approximate a deleted leaf node ratio of 40% (I realize that this depends on the number of levels in the index, etc..)

Any other ideas about this method would be appreciated.

Thanks,

Ishay Friedman Received on Tue Oct 23 2001 - 13:09:11 CDT