Indexes – PCTUSED 0 Why???

PCTFREE and PCTUSED are the two space management parameters in manual management of blocks, that control the use of free space for inserts and updates to the rows in all the data blocks.

The PCTFREE parameter sets the minimum percentage of the space in the data block to be reserved for possible updates to the existing rows.

The PCTUSED parameter sets the minimum percentage of the space used by the existing rows plus overhead before new rows can be added to the block.So,PCTUSED determines when the block comes back in the Freelist.

Let  us create an index using these two parameters, individually :-

sys@test>create index test.ap_cnt on test.ap(cnt) pctfree 40;

Index created.
sys@test>drop index test.ap_cnt;

Index dropped.
sys@test>create index test.ap_cnt on test.ap(cnt) pctused 40;
create index test.ap_cnt on test.ap(cnt) pctused 40
*
ERROR at line 1:

ORA-02158: invalid CREATE INDEX option
sys@test>

Now the interesting question is “WHY CAN’T WE USE PCTUSED PARAMETER  FOR INDEXES??

Just going through the OTN Database General Forum i found the same question asked by Aman long back in 2007 , and a wonderfull explanation giving by Howard Rogers.The answer is :-

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 deleted 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 organized 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 accommodate 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 reorganization 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.

And that is what is said a “Stunning Explanation”  🙂

One thought on “Indexes – PCTUSED 0 Why???

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s