Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: INIT initial Extend For Primary Key Index ??
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...
Received on Tue Aug 03 1999 - 02:50:57 CDT