Re: Analyzing indexes

On Tue, 11 Mar 2003 14:38:05 +0000, Chuck wrote:

> I need to determine whether or not a couple of indexes need to be
> rebuilt.

They almost certainly won't be!

What makes you think they might need it? If this is an index on a monotonically incrementing sequence number, and you've been doing sporadic deletes of table records with low sequence numbers, then yes, you might have a need.

But if the data coming into the table is randomly distributed ("Mary" one minute, "Adam" the next, "William" the next, "Edward" and so on) then the new records will make use of space created by earlier deletes (the final insert of an "Edward" there would re-use a space previously created by the deletion of, say, a "Daniel", for example. Probably).

If you have an index on a monotonically incrementing sequence number and you do a bulk purge of early records from the table, then the chances are that you've cleared out entire index leaf nodes -which means they can now be reused for new inserts. And all without any extra effort on your part.

Meanwhile, Oracle has automatically been re-balancing your index for you, and has kept it as efficient as it can be under the circumstances.

There are few good reasons for rebuilding indexes (moving them to a different tablespace is one, or converting it into a reverse key index perhaps).

>The problem is the indexes are quite large and on a 24x7 high
> volume database. If I try to run an "analyze validate structure" to
> gather the data I need to make that decision, it sets a lock on the
> table for about an hour which I can't afford to do. There is no slow
> time when I can do this and management has said before they're not going
> to spring for the partitioning option to break the indexes up into
> managable pieces. Is there some other way I can get the information
> needed to determine if an index needs to be rebuilt or not without
> setting a lock on the table? We are on Oracle 8.1.7.

I first of all wouldn't worry about it. Validate Structure is only used to populate INDEX_STATS, where you are then supposed to compare LF_ROWS with DEL_LF_ROWS: where the ratio of deleted leaf rows to leaf rows exceeds about 20% you are supposed to rebuild. Supposed being the operative word: it's a pointless rule of thumb, and doesn't take account of the reuse of deleted leaf row space I mentioned earlier.

Second, if you are genuinely concerned that the index is wasting space, you could do an 'alter index blah coalesce;'. Coalesce is an inherently online operation, and where it encounters two adjacent half-empty leaf nodes that could be combined into one fairly full one, it will do so, freeing up one of the nodes in its entirety, and thus making it available for fresh inserts independent of their actual value. Under the right circumstances, you could follow this up with a 'deallocate unused' command to get the space reclaimed for the use of other segments entirely.

But third, if you are desperately wanting to analyze your indexes, then you have to live with the exclusive locking. There's no way around it in 8i.

Regards
HJR Received on Tue Mar 11 2003 - 13:53:04 CST