Re: Index Leafs don't empty on delete why pls help....

Peter, if all of the associated table entries covered by the index entries in a leaf block are deleted the leaf block will be placed on the free list for reuse. But if even one key remains the block will hang around forever being reusable only if the same key values are re-created at some point.

You can rebuild or coalesce the index as previously mentioned; however, on some early vesions of 9.2 there is a bug where the coalesce command can result in corruption to the rdbms dictionary. For this reason I recommend you avoid the command until such time as you search metalink for the bug details. I am pretty sure it is fixed by 9.2.0.6.

While having to rebuild indexes that do not reach a steady state is a pain sometimes I would point out that SQL Server and DB2 documentation recommneds rebuilding indexes on a regular basis. Most indexes in Oracle do not need to be rebuilt very often. Some may.

The reverse index option is not suitable for indexes used to support range scans unless there has been a change in the more recent versions.

HTH -- Mark D Powell -- Received on Fri May 12 2006 - 09:18:17 CDT