Re: pctfree setting for BTREE indexes

pelt01_at_my-deja.com wrote:
>
> Using Oracle 7.3.4.4, I'm creating an index on a table that is subject
> to a significant number of inserts. As I understand from an Oracle
> Note, if the pctfree setting is too low, and the inserts are not
> monotonic then the low pctfree setting will more quickly result in leaf
> block splits.
>
> Is there any method of estimating a suitable value for pctfree (e.g.
> 10%, 20%, or 50%) given that I am able to analyse the index on an
> existing test system? For example, is the information in index_stats,
> or any other statistical view, of any use in telling me whether the
> existing test index has suffered from leaf block splits.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

A b-tree usually settles down over time to 20%-30% free in the blocks, so a pctfree of 20-30 is a good starting point. But your knowledge is the most important thing - eg if it a monotonically increasing sequence, then set pctfree to much lower (possibly even 0)

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse