Deleted Index Entries Part IV (Breaking Glass)

Yet another method of cleaning out deleted space Oracle has up its sleeve is the recycling of index blocks that contain nothing but deleted index entries.

In some cases, it’s possible for an index block to contain no current index entries with all the corresponding index entries within the index block having been deleted. The index block may be totally empty of index entries or it may contain just deleted index entries.

Once an index block has no current index entries, Oracle places the block on the segment freelist and is now a candidate block to be recycled and reused elsewhere within the index structure after a subsequent index block split operation.

When recycled, the index block becomes “unattached” from its current location within the logical index structure and is reallocated elsewhere within the logical index structure as the new index block in an index block split operation.

Any previously deleted index entries are removed and the contents of the index block are replaced with new index entries associated with its new logical location within the index structure.

A simple little demo to illustrate this process.

First, I create a simple table and associated index and populate it with a 10000 rows:

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

Table created.

SQL> INSERT INTO test_empty_block SELECT rownum, ‘BOWIE’ FROM dual
     CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

SQL> CREATE INDEX test_empty_block_idx ON test_empty_block(id);

Index created.

I next delete the vast majority of the rows, leaving only a handful behind that are likely only found in the last one or maybe two leaf blocks within the index. All the other index leaf blocks therefore only contain nothing but deleted index entries:

SQL> DELETE test_empty_block WHERE id between 1 and 9990;

9990 rows deleted.

SQL> COMMIT;

Commit complete.

If we look at some statistics, we’ll find we have lots of deleted row entries that are all found in leaf blocks that are totally empty, except perhaps the right most leaf block within the index:

SQL> ANALYZE INDEX test_empty_block_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT lf_blks, del_lf_rows FROM index_stats;

LF_BLKS DEL_LF_ROWS
------- -----------
     21        9990 

We next insert a bunch of new rows into the table, but importantly, all these new rows have index entry values that are greater than the previous values. Therefore, all these new index entries will be inserted into the right most side of the index structure and not into the index where we have nothing but the previously deleted index entries. 

Oracle will need to allocate new index leaf blocks to accommodate these new index entries, but from where will Oracle get these new index blocks ?

SQL> INSERT INTO test_empty_block SELECT rownum+20000, ‘ZIGGY’
     FROM dual CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

If we now look at the index statistics, we notice something very interesting:

SQL> ANALYZE INDEX test_empty_block_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT lf_blks, del_lf_rows FROM index_stats;

LF_BLKS DEL_LF_ROWS
------- -----------
     21           0

The number of deleted leaf entries has disappeared back to zero and the number of allocated leaf blocks has remained the same at 21.

Oracle has both removed the previously deleted index entries and has also recycled all the previously empty leaf blocks and reused them again to accommodate the new index entries. The index is effectively the same size as it was previously even though we’ve added new values that were greater than the previously deleted values.

So index blocks that are totally empty or contain nothing but deleted index entries become “free” again, are placed on the freelist within the index segment and can be reused or recycled again somewhere else within the logical index structure at some later point in time.

Again, yet another example of Oracle cleaning out these unwanted deleted index entries for us.

However, these empty index blocks can potentially be problematic and can cause performance issues until eventually they actually get reused and recycled.

But that’s a topic for another day.