Using DBMS_SPACE.UNUSED_SPACE to Measure Index Quality, Lock Table?
Date: 23 Oct 2001 11:09:11 -0700
Message-ID: <722d1e1e.0110231009.54897373_at_posting.google.com>
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.