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

"peter" <hhh.database_at_gmail.com> wrote in message news:1147426339.890802.326910_at_i40g2000cwc.googlegroups.com...
> Hello Respected DBAs,
>
> When we deleted rows from table, in index those entries are only marked
> as delete but not deleted why.........what is the use of it.
> Even We have to rebuild it again and again if delete % is high......
> Is it disadvantage of Oracle ......
> Please Inform.
>
> Thank You
>

Marking the entries as deleted probably made it easier to design and implement the read-consistency and rollback code. A similar feature appears in table deletions - but the impact is less noticeable; the sequence for tables is:

    mark as deleted
    delete row but leave row-stub at rowid location     reuse rowid location

Since index entries don't have static locations, it is not possible to emulate the 'leave stub' step. So deleted index entries can stay visible for much longer than the matching table entries.

In general, marking the rows as deleted and only clearing up when the space is needed is not a problem. The space will always become re-usable at a later point in time. But it is possible to build examples where "later" is much later than you would like - in which case a rebuild (or coalesce, usually) may become desirable.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html