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

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.

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------