Re: Fragmentation in Oracle

Tom,
As far as I'm aware, index space can only be re-used if appropriate space is available in the correct area of the ordered sequence set. You are correct that the statement 'Index space IS NEVER REUSED' is incorrect. It is never reused in the sense that PCTUSED is inappropriate. An index block can only be reused for index entries which should reside in that block as a result of their key value. However, indexes do sometime grow infintely large over time even if rows are deleted, if the index is on an ever-increasing key, for example. There are situations where the *only* way to re-claim space in and index is to rebuild it, or at least that's what we used to teach people on Oracle's own Oracle7 Database Administration courses in the UK.

I think the point here is that index space usage is directly related to the type of key and distribution of key values and certain indexes will not be helped by row deletions. This would explain why you both had different results from your tests.

Hope this helps,

Doug Burns. Received on Sat Nov 08 1997 - 00:00:00 CET