hey marist I thought deleted indexes are never reused and thatīs why pctused is always 0?
Quoted from : Note:108573.1 on METALINK
Indexes will become fragmented with inserts and deletes because
of the way indexes are implemented. When a row is deleted,
Oracle will not reuse the index space. Pctused for indexes
is always 0, which means the index blocks will not be put on
the free list for reuse. Therefore, indexes are always growing
and can become very fragmented. Hence we have to drop and
recreate indexes at times. This articles explains when we should do it.
This contradicts from the mail LND mentioned from IXORA
Quoted from IXORA:
If a table has 100,000 rows and 99,999 of 100,000 rows and index entries are
deleted. How is the index balanced?
In this case the rows are deleted from the index, and the empty blocks inserted
onto the index free list. These blocks are still part of the index and will
need to be accessed when traversing the index.
One says blocks wont be put on free list and the other says the blocks are inserted in the freelist....?