Why does Oracle not permit the use of PCTUSED with indexes?

Imagine you've got an index on people's names. And, just for the sake of argument, only 4 entries can fit in a block.

So you have

Adam
Bob
Charles
David

and

Edward
Frank
Graham
Howard

You want to insert a new entry in the table (and hence into the index on that table) of Zebedee.

As things stand, both blocks of the index are full, so Zebedee has to go into a third block all on his own.

Now say you delete some existing rows:

delete from employees where name in ('Bob', 'Charles', 'David'). You're left in this state:

Block 1:

Adam

Block 2:

Edward
Frank
Graham
Howard

That means block 1 has 3 empty slots for new entries. So you still want to insert a record for someone called Zebedee: can it go into the first block, with all that empty space? No. Because if it did, you'd end up with an index that ran:

Adam
Zebedee
Edward
Frank
Graham
Howard
...and you might notice that the alphabetical order has been screwed to buggery at this point. So no, although the first block contains lots of empty space, it's still defined as an "A-ish" sort of block. It actually has to have entries between Adam and Edward, and it can't suddenly decide to accept entries from Wilma, Susan or Mary.

So when can block 1 be used to house those sorts of entries? When Adam gets deleted. Because when that row goes, you're in this position:

Block 1: completely empty
Block 2: values from E to G

Being completely empty, there's no "A or B'ness' about block 1 any more. So suddenly, it can accept any entry at all. Now, physically, block 1 might come "before" block 2... but we jig things around in the root and branch blocks so that doesn't matter. Therefore, you can now insert all those other records you wanted and get this situation:

Block 1:
Mary
Susan
Wilma
Zebedee

Block 2:
Edward
Frank
Graham
Howard

And we have a branch block (actually, in this case, the root node) which says:

A -> L: goto block 2
M -> Z: goto block 1

So, once we deleetd Adam, we were allowed to insert new items into all that empty space. But of course, once we deleted Adam, the first block was completely and utterly empty. And what does a completely empty block equate to in PCTUSED terms? Er, 0%.

It has to be 0% PCTUSED, in other words, because entries in an index have to be organised according to some order. Allowing you to reuse space 'out of order' would destroy the point of an index. And you can only say, "Order is irrelevant at this point" when no entries exist at all... which equates to PCTUSED of 0.

PCTFREE is fine to set, because that simply says when to stop filling one block and start filling another. In indexes, it reserves space that might come in handy for preventing block splits. So there's a use for PCTFREE, definitely. Imagine we'd set PCTFREE of 25%. Then the earlier index would be:

Block 1:

Adam
Bob
Charles
Block 2:
David
Edward
Frank
Block 3:
Graham
Howard

With a PCTFREE of 25% and only 4 entries allowed per block, we end up only storing 3 entries per block: the missing entry being reserved as 'free space'. Now insert a guy called 'Brian': is there room to fit him between Bob and Charles? In this new index, yes: block 1 is allowed to grow to holding 4 records, and there's only currently 3, so yup: there's room to accomodate the new guy.

But in the earlier example? No, the block was allowed to hold four records. It's holding four records. You want to insert a new one that HAS to go between two existing records... we have to perform a block split and reorganisation to allow that to happen. Block splits are costly affairs. PCTFREE would have saved us from having to do one.

So yes, PCTFREE in an index is useful. PCTUSED is meaningless. Once you impose an order on rows, they cannot just go anywhere. Space reserved for future inserts is fine. But a flag to say 'insert away!' when inserting would disrupt a carefully-achieved ordering is a complete no-no.

Hope it is understood...