Re: Re-use of Leaf Nodes

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 13 May 2004 09:15:33 +1000
Message-ID: <40a2b009$0$23828$afc38c87@news.optusnet.com.au>

ctcgag_at_hotmail.com wrote:

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote:
> 

>>I think there's a relationship there, don't you? I wonder what happens
>>when you build the index (initially AND at the end) with PCTFREE of,
>>say, 30%? (Yikes. I can't ask questions like that and not find out, so
>>OK, I did the test and we get an end-of-test index inflation of ...[wait
>>for it].... 30% (a 480 block index became a 690 block one).
>>
>>I still can't see why the index balloons by PCTFREE with a rebuild when
>>the loading of records into the index should have been respecting
>>PCTFREE in the first place anyway (except that the figures suggest it
>>wasn't) .... My head hurts.

> 
> 
> But PCTFREE is only used during rebuild (or initial build) of the index.
> (And maybe append?)  The index would not be respecting PCTFREE in the first
> place, because "The first place" was a bunch of ordinary inserts; at least
> if I am following the thread properly.  Afterall, what are you saving that
> space for?  For using it during inserts!

Well, because we don't in principle know that the index is on a sequence number, we're leaving it there for subsequent inserts into earlier blocks which we might make which would otherwise cause block splits. That's what I always thought, anyway: That PCTFREE would be respected for ordinary inserts, with the proviso that an insert into the index *must* always be housed in a positionally(ie value)-significant leaf node; and if the need arises, therefore, ignore PCTFREE, until you are forced to perform a block split because the node is completely full.

The maths on the other hand suggests you're right, of course: Specify a PCTFREE when creating an empty index; load the table (yes, with ordinary inserts) and have PCTFREE utterly ignored on the index for that load; rebuild and have PCTFREE respected, and thus consume 10% more space than before.

However, the documentation states

"PCTFREE has the same function in the statements that create and alter tables, partitions, clusters, indexes, materialized views, and materialized view logs."

And in a table, PCTFREE would stop a block (leaf node for an index) being filled completely at the 90% mark (assuming a default PCTFREE, of course), even for ordinary inserts. I think that counts as a documentation bug, in that case.

Oh well, live and learn, I guess.

Regards
HJR Received on Wed May 12 2004 - 18:15:33 CDT