Re: Index Fragmentation

From: Venkat <vreddy_at_ix.netcom.com>
Date: 1998/03/22
Message-ID: <6f4a9k$q6n@sjx-ixn6.ix.netcom.com>#1/1

Jeff,
Check out the data dictionary table INDEX_STATS. Use ANALYZE INDEX indexname VALIDATE STRUCTURE and get the statistics from the above mentioned table (This table contains information about the last ANALYZE INDEX only!!). Then calculate the ratio of the number of unused leaf nodes that were previously used (del_lf_blk) to the number of used leaf nodes (lf_blk)) from INDEX_STATS. If the ratio approaches 30% (i.e 30% of index entries are unused) then it is time to rebuild the index. HTH
regards
Venkat

>Does any know of the easiest way to determine if an
>index is fragmented? I am trying to find the easiest
>way to determine when an index is a candidate for a rebuild.
>We have several volatile tables and index fragmentation
>is a big problem. Any help or pointers would
>be greatly appreciated.
Received on Sun Mar 22 1998 - 00:00:00 CST