One of the things I’ve seen at a number of sites is the almost fanatical drive to make indexes as small as possible because indexes that are larger than necessary both waste storage and hurt performance.

Or so the theory goes … 🙂

In many cases, this drives DBAs to create or rebuild indexes with a PCTFREE set to 0 as this will make the index as compact and small as possible.

Of course, this is often the very worst setting for an index to remain small because the insert of a new index entry is likely to cause a 50-50 block split and result in two 1/2 empty leaf blocks (unless the index entry is the maximum current value). Before very long, the index is back to a bloated state and in some sad scenarios, the process is repeated again and again.

A point that is often missed though is that it doesn’t even take an insert to cause the index to expand out. A few delete statements is all that’s required.

To illustrate I create my favorite little table and populate it with a few rows:

We note the index only has 19 leaf blocks and that most leaf blocks have 533 index entries and only an avs (available free space) of some 10 or 11 bytes. Only the last leaf block is partly full with some 2019 free bytes.

That’s fantastic, the index really is a small as can be. Trying to use index compression will be futile as the indexed values are effectively unique.

I’m now going to delete just a few rows. Surely deleting rows from the table (and hence entries from the index) can only have a positive impact (if any) on the index structure.

The differences I see are:
– kdxcolok changes from 0 to 1. According to your description in https://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf, page 16ff that “denotes whether structural block transaction is occurring”. Ok, maybe.
– kdxcosdc: that’s a “count of index structural changes involving block”. Sounds plausible.
– the ITL entries: now I could reread the details in Oracle Core, but my first suspect is Lck = 1 – and I guess that’s the sign of a lock byte that had been added to an index entry; and though the corresponding entry is no longer in the block the lck entry remains there as a task for following DML operations. So I guess the lock byte is “the straw that breaks the camel’s back” (is that really an idiom as my dictionary tells me?) and the reason for the 50-50-split.