Re: Indexes - PCTFREE should be small?

robertchung0909_at_my-deja.com wrote:
>
> This is my understanding: Indexes never shrink, but only grow --->
> Having free space does not help ---> Therefore PCTFREE for indexes
> should be small.
>
> I believe above is right. Having free space for *tables* do help
> because they do delete/update/insert data and Oracle can simply put
> additional data to current data block instead of chaining data to new
> datablock if free space is available in current block.
>
> Assuming so, it is logical to choose small PCTFREE (even 0) for
> indexes. However, virtually everyone that I know assigns 10 for
> PCTFREE for every index. Could someone comment on this? Thank you in
> advance.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

This is not correct...you can prove this for your self easily enough with the following simple test:

1. create a table indexed on column "x"
2. insert the numbers 1 - 100,000 into x and commit
3. analyze to see the blocks in the index
4. delete rows 1 - 20,000 and commit
5. insert rows 100,001 - 120,000 and commit
6. re-analyze and you'll see that space get's re-used

An B-tree typically runs at about 75% full assuming a 'random' distribution of keys. For data warehouses etc where everything stays static then pctfree 0 may be a good choice, but if you nothing about the data distribution than pctfree 20-25 may be a good place to start

HTH

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

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