Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Indexes - PCTFREE should be small?

Re: Indexes - PCTFREE should be small?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 16 Sep 2000 00:19:00 +0200
Message-ID: <969081698.20639.0.pluto.d4ee154e@news.demon.nl>

<robertchung0909_at_my-deja.com> wrote in message news:8pu320$oag$1_at_nnrp1.deja.com...
>
>
> 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.

Having free space *does* help if you know the index is going to grow. It will leave free space in every index block, and it thus will leave empty buckets in all leaf rows of the index. This will mean the index will not immediately extend, instead an empty bucket will be used for the new key. This idea is not going to work if *all* of your inserts are on one index of the index.
So if you have a table where only inserts take place, and if you don't *increase* pctfree, the final index will be bigger than the index with pctfree increased.
So I wouldn't recommend to *ever* decrease the pctfree to 0, and I would recommend to increase it in tables which have only inserts (and sometimes deletes) and no updates.

Hth,

Sybrand Bakker, Oracle DBA Received on Fri Sep 15 2000 - 17:19:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US