Deleted Index Entries Part III (Slip Away)

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.

SQL> COMMIT;

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:

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

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

SQL> COMMIT;

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:

LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
------- ----------- ---------------
    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 …