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

Home -> Community -> Usenet -> c.d.o.server -> Re: Large tables and Primary keys

Re: Large tables and Primary keys

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 9 Jan 1999 16:32:42 -0000
Message-ID: <915899672.11930.0.nnrp-12.9e984b29@news.demon.co.uk>

For indexes, PCTFREE applies only to index creation on data that is already in place, and not to subsequent running. The only reason to set it to anything other than zero is to ensure that the users don't get a spurious degree of efficiency on day 1, which then degrades to the 'proper' level over time.

As a rule, I usually create indexes on existing datasets with a PCTFREE of 25, as this is the 'normal' level to which a B-tree index is supposed to degrade with 'normal' use. On the other hand your aplpication is not quite normal, and I would probably set it to a lower value such 5.

You also should be looking at some form of time-partitioning to handle two-years of data, otherwise when you start deleting the job is likely to be very slow, and leave you with a lot of space that can only be re-used quite slowly.

Jonathan Lewis:

Yet another Oracle-related website: www.jlcomp.demon.co.uk

Ian Downham wrote in message <36976DD4.C46C83B1_at_which.net>...
>Can anyone put me on the right track, please ?
>
>My problems is I don't know how indexes are built (internally) and so
>have
>no idea how find the correct value for PCTFREE when creating the Primary
>keys
>on the tables.
>Since the keys, when generated, will be in groups (as above) and will
>always
>be sequential do I have a problem with splitting index blocks if I get
>the
>PCTFREE wrong or could I just make it ZERO ?
>We will be, at some time, dumping the data to flat files then
>housekeeping /
>deleting the data (will this cause a problem with index space) ?
>
Received on Sat Jan 09 1999 - 10:32:42 CST

Original text of this message

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