jump to navigation

Deleted Index Entries Part III (Slip Away) June 23, 2008

Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths.

Another little post while I look after some unwell munchkins …

I’ve already looked at the most common example of when Oracle will automatically clean out deleted index entries, that being any subsequent insert into a leaf block will clean out the deleted entries that may exist from the associated leaf block.

Another example of Oracle automatically removing deleted index entries is that associated with a variation of delayed block cleanout. If an Oracle index block with deleted index entries is written to disk before the associated transaction performing the index delete operation is committed, the next time the index block is accessed, Oracle will not only clean out the transaction details from the index block (such as the lock byte) but the deleted index entries themselves may also be cleaned out as well.

This scenario is most likely to occur during large or long running transaction operations (such as batch operations) where many rows are likely to be accessed and/or modified and the associated modified index blocks may get aged out of the buffer cache and written to disk before the transaction ends via the COMMIT.

Note this delayed clean out does not require the index block to be accessed via a subsequent DML operation, even a simple SELECT statement will be sufficient to perform the necessary clean out of deleted index entries.

To illustrate this behaviour, basically create a table with a bunch of rows, deleted some of them but flush the buffer cache prior to issuing the commit on the delete.

SQL> CREATE TABLE del_stuff (id NUMBER, name VARCHAR2(30));

Table created.

SQL> CREATE INDEX del_stuff_i ON del_stuff(id);

Index created.

SQL> INSERT INTO del_stuff SELECT rownum, ‘Bowie’ FROM dual CONNECT BY level <=1000;

1000 rows created.


Commit complete.

Next, deleted say 1/2 of the rows from the table.

SQL> DELETE del_stuff WHERE mod(id,2) = 0;

500 rows deleted.

At this point, we flush the associated blocks to disk to simulate a large or long running transaction is which blocks may be aged from the buffer cache and written to disk before the COMMIT is performed.

SQL> ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;

Session altered.

in 9i, or since 10g:


System altered.

It’s only at this point after the blocks are flushed to disk that the COMMIT is performed.


Commit complete.

Once the index blocks are subsequently accessed, we notice the deleted index entries may have already been cleaned out …

NOTE: The following results do not consistently occur if the index consists of just a single block (the root block is a “special” case),  but does appear to be more consistent if the index has a blevel of one or more (as in the demo) and as would be more typical with indexes involved in long running transactions.

A view of INDEX_STATS after a ANALYZE … VALIDATE STRUCTURE command will show the following:

------- ----------- ---------------
    500           0               0

Note: There are no deleted index entries, none. They’ve already been cleaned out.


A treedump will show the following:


—– begin tree dump

leaf: 0x1402e4a 20983370 (0: nrow: 500 rrow: 500)

—– end tree dump


Note: It only shows 500 rrow and 500 nrow values, clearly highlighting there are no deleted index entries.


A partial index block dump will show the following:


kdxlende 0


The deleted index entry count kdxlende is 0, with no deleted index entries existing in the block. None.


All the deleted index entries have already been cleaned out, with not a subsequent DML operation in sight.


So yes, again Oracle can clean out deleted index entries as part of it’s general processing so that the need to do so manually via an index rebuild, coalesce or shrink is a somewhat rare occurrence.


But wait, there’s still more cases to come when Oracle will simply automatically remove deleted index entries entries …


1. Asif Momen - June 23, 2008

Hi Richard,

Well, I did get DEL_LF_ROWS = 0 on the first run but since then Oracle is still reporting DEL_LF_ROWS = 4.

SQL> select * from v$version;

Oracle Database 10g Enterprise Edition Release – Prod
PL/SQL Release – Production
CORE Production
TNS for 32-bit Windows: Version – Production
NLSRTL Version – Production

SQL> conn test/test
SQL> CREATE TABLE del_stuff (id NUMBER, name VARCHAR2(30));

Table created.

SQL> CREATE INDEX del_stuff_i ON del_stuff(id);

Index created.

SQL> INSERT INTO del_stuff SELECT rownum, ‘Bowie’ FROM dual CONNECT BY level COMMIT;

Commit complete.

SQL> DELETE del_stuff WHERE id in (2,4,6,8);

4 rows deleted.


System altered.


Commit complete.

SQL> analyze index del_stuff_i validate structure;

Index analyzed.

SQL> select LF_ROWS, DEL_LF_ROWS, DEL_LF_ROWS_LEN from index_stats;

———- ———– —————
10 4 56


What’s going wrong?


2. Richard Foote - June 23, 2008

Hi Asif

What’s possibly gone wrong is that I’ve found a number of scenarios and Oracle versions where the buffer cache isn’t always cleared as expected and/or the session doesn’t perform the necessary physical I/Os for this to work.

Rather than use the same session to flush the buffer cache, try and use another session and just make sure the blocks have indeed been flushed from the cache and need to be re-read subsequent to the commit.


3. Asif Momen - June 23, 2008

Hi Richard,

No luck trying when buffer cache was flusded from another session.
Yes, blocks indeed are flushed out of buffer cache.

SQL> select rownum,
2 addr,
3 decode(state, 0,’free’, 1,’xcur’, 2,’scur’, 3,’cr’, 4,’read’, 5,’mrec’,
4 6,’irec’, 7,’write’, 8,’pi’) state,
5 tch,
6 tim
7 from x$bh b
8 where obj = 64946;

———- ——– —– ———- ———-
1 0508A140 free 0 4294967295
2 0508A140 free 0 4294967295
3 0508A140 free 0 4294967295
4 0508A140 free 0 4294967295
5 0508A140 free 0 4294967295
6 0508A140 free 0 4294967295
7 0508A140 xcur 2 1214233850
8 0508A140 free 0 4294967295

8 rows selected.


I wonder how did it ran successfully on the first occassion.


Asif Momen


4. Richard Foote - June 24, 2008

Hi Asif

Thank you very much indeed, I ran the demo on a windows (close) database and have managed to reproduce your results. However, after playing around with it for a while, I have also managed to make it work perfectly as well on the same configuration so the results are clearly not consistent with it working sometimes and not at other times.

I’m not entirely sure at this stage what I do differently although I notice is some of my tests after I flush the buffer cache and the buffers are freed, a read forcing the index still produces no PIOs so I suspect it has something to do with the flushing mechanism not forcing PIOs as it should and hence the clean outs not occuring.

I need to do some more digging but thank you again so much for your input, it’s much appreciated.

I’ve modified the post to suggest the demo as described may only work.


5. Richard Foote - June 26, 2008

Hi Asif

I’ve modified the demo so that more rows are inserted into the table and the associated index consists of more than just a single block, blevel 0 index.

The root block is a special index block in that it never changes so that Oracle always knows from where to start an index scan, without having to visit the segment header first. I think this has something to do with the delayed block cleanout not working in some environments, in some cirumstances as details remain cached regardless.

By having a blevel of 1 or more, the effects of delayed block cleanout appear to be more consistent. I would be intertesed in knowing whether by creating such an index, you get the same results as well.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: