pctfree setting for BTREE indexes 
Author Message
 pctfree setting for BTREE indexes

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.***.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 pctfree setting for BTREE indexes

Quote:

> 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.***.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.***.com/

We are born {*filter*}, wet and hungry...then things get worse



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. PCTFREE 1, Any advantage of using Pctfree 1

2. Oracle PCTUSED/PCTFREE settings

3. index pctfree

4. multi-column btree index for real values

5. Btree index extension question

6. Problem with btree index on 7.1.3

7. patch to allow btree indices on BYTEA

8. btree-indexing-engine

9. Database/Structures and Indexes - Btree?

10. Displaying index BTree

11. Problem with btree index?


 
Powered by phpBB® Forum Software