| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: INIT initial Extend For Primary Key Index ??
Jonathan Lewis wrote:
>
> There is an argument for creating an index with
> a PCTFREE of 25 as a general rule of thumb.
>
> If you create an index with PCTFREE of 0, the
> very next row insert will split one block, leaving
> you with 2 blocks at 50% usage (that's actually
> both a generalisation and simplification), and it
> might not take long before lots of index blocks
> split and the performance of the index drops
> dramatically.
>
> Since the 'average' b-tree index runs at 75% efficiency,
> it is quite a good idea to start at 75%.
>
> Times to ignore the rule of thumb.
> a) When you are never going to insert rows,
> or update columns that appear in the index.
>
> b) When you are only going to be adding rows
> at the high end of the index (e.g. sequential PK)
>
> c) When you know the proper running usage figure
> for the index, and build it at that instead of 75%.
> This is the tricky one to get right.
>
> P.S. If someone says they are NEVER going to insert
> rows, I usually build at 1% free anyway.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Connor McDonald wrote in message <37A5A7EE.4D92_at_yahoo.com>...
> >
> >I'd recommend PCTFREE 0 on indexes since a block won't get re-used until
> >its totally empty anyway...
Thanks for the reminder Johnathon...
I've spent too long adding indexes post-processing on data warehouses..
:-)
--
"Some days you're the pigeon, and some days you're the statue." Received on Tue Aug 03 1999 - 06:51:34 CDT
 |
 |