Re: pctfree parameter for PK indexes based on running sequences.

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 24 Sep 2005 13:28:14 -0700
Message-ID: <1127593632.579679@yasure>

Connor McDonald wrote:
> DA Morgan wrote:
>

>>nirav wrote:
>>
>>>Hello Experts,
>>>
>>>We have around 400 primary key indexes,all are based on running
>>>sequences. The DDL that creates these sequences has pctfree of 10%.
>>>I recently read that pctfree is absolutely meaningless for indexes that
>>>are based on running sequences ...because it is meant to be there to
>>>allow for new inserts/updates etc , but in the case I refer, these
>>>updates will not be in existing nodes , they will allways be in the
>>>last leaf node ..So the conclusion is that for these indexes the
>>>pctfree of 0 could have saved us lots of space.
>>>
>>>I would be thankful if you can share your views on this.
>>>
>>>S.
>>
>>Discussing PCTFREE and SEQUENCES in the same paragraph is as
>>meaningful as discussing drain cleaners and WHERE clauses together.
>>
>>PCTFREE in any primary key index should be set to 0 if the index
>>is a true primary key. In your case, with surrogate primary keys,
>>it should be 0 too. And it does matter unless you have some reason
>>to think that reading vacuum makes sense.
>>--
>>Daniel A. Morgan
>>http://www.psoug.org
>>damorgan_at_x.washington.edu
>>(replace x with u to respond)

>
>
> I think
>
> "PCTFREE in any primary key index should be set to 0 if the index is a
> true primary key"
>
> is too strong a statement.
>
> Consider the scenario
>
> - table has lots of data
> - index needs to be rebuilt (eg, had to move table)
> - table will continue to have data added to it
>
> what happens then ? You've got an index thats packed at 100% with
> possibly more PK's coming in. That could be a nasty block splitting
> headache.

Source:
http://asktom.oracle.com/pls/ask/f?p=4950:8:2151989447892353050::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2424495760219

"All the pctfree does on an index is defer the splitting of an index block due to subsequent inserts/updates AFTER the index is build."

Assuming the primary key is a true primary key ... especially a surrogate key generated by a sequence ... what is the value in leaving empty space in the block. With a true primary key inserts may, indeed, take place. But updates should never happen.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Sat Sep 24 2005 - 15:28:14 CDT