Re: EASy question : INDEX PCTFREE 0%

In article <502nbt$n4u_at_mtinsc01-mgt.ops.worldnet.att.net>

           docjones_at_worldnet.att.net "Roy Jones" writes:

> Huijbrechts Rolf <huybrecr_at_realsoftware.be> wrote:
>
> >Hi,
 

> >I'm a beginner in Oracle DBMS and I want to speed up transactions on a
> >database.
 

> >I assume that the PCTFREE creation parameter can be 0 for an index as
> >the index blocks are never updated. The length of the primary key is
> >always the same in this database. I also assumed that in this case the
> >Oracle index blocks would contain more keys as more physical space can
> >be used ans so the system would respond equally to transactions and
> >faster for queries via that index.
 

> >Local benchmarks show that inserts and updates on that table are slower
> >with Indexes with PCTFREE 0% than ones with PCTFREE 10%.
 

> >Who can help me in finding the cause for this ?
 

> >Thanks in advance.
 

> >Rolf Huijbrechts
> >REAL SOFTWARE
> >huybrecr_at_realsoftware.be or 101505.533_at_compuserve.com
> >-------------------------------------------------------------
> >All opinions expressed are mine and not those of my employer.
> >-------------------------------------------------------------
>
> PCTFREE only applies to the creation of indexes. When you update a
> table with indexes, Oracle inserts into the index the appropriate
> value. If PCTFREE is set to 0, when you update/insert on that index
> Oracle must allocate a new block. As a general rule of thumb you
> should set your PCTFREE to the percentage of fullness on your table
> ie Your table is 50% full set your index PCTFREE to 50. Also you
> should drop and recreate your indexes often if you do a lot of DML on
> your table.
>
>
>
> Roy Jones
>
>

        As I recall it PCTFREE applies to the datapages used to store oracle data. Data pages (equivalent to 512bytes of data) are filled up in chronalogical order (ie as you enter data pages fill up and new data pages are assigned). Deleting data does not free space.

        The idea of PCTFREE is to pre-allocate dataspace in pages in order that updates to existing records can be placed in the same datapage as the original record and not in a distant datapage.

        This is to preserve performance following updates. and before house keeping activeties straighten things out.

        The trick is to guess the number of updates a record set (within a page) might have within this time frame and workout how much free space you WANT to provide.

-- 
steve roberts