Re: EASy question : INDEX PCTFREE 0%

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 Received on Thu Aug 29 1996 - 00:00:00 CEST