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....?

[Edited by pando on 04-06-2001 at 10:59 AM]

04-06-2001, 12:07 PM

LND

i would trust ixora more. in fact, this site and http://www.jlcomp.demon.co.uk are quite critical to many books/docs on oracle both by oracle and others.
And it seems thay have a point in most cases.

04-06-2001, 12:13 PM

pando

Well I think the Note has a point stating why PCTUSED is always 0 for indexes no?

04-06-2001, 12:42 PM

LND

but where on metalink article 108573.1 it is stated that index blocks can not be reused FOR THE SAME INDEX?

04-06-2001, 12:46 PM

pando

well the point is even the index are NOT the same the index is being reused if you check my previous post with example

I have 100 rows
i deleted 50 rows, 51 to 100
I added again 51 rows but 200 to 250, the index space were reused