Re: Large tables and Primary keys

Without belaboring the issue, the simplest way to determine density of data is to create some sample data and analyze the object. Then read the statistics and go from there.

Don't overpack indexes. Leave plenty of free space to allow for inserts of new values such that splitting is minimized. Do you really need all that data to uniquely identify rows? Would a sequence do to establish a primary key? Then, you might build a smaller, more selective index that you could query from.

Roger Snowden
Sr. Systems Engineering Specialist
Oracle Corporation
rsnowden_at_NOTus.oracle.com <-- to reply, remove the obvious

Ian Downham wrote:
>
> Can anyone put me on the right track, please ?
>
> We have three Alphas running VMS 7.1 and ORACLE 7.3.2
> The system produces data locally as a result of recruitment operations
> (Home
> Shopping Catalog) and this is then transfered (near real time) to
> another
> Alpha for later use.
> This data is to be stored for upto two years.
> As part of the process a key is produced (enquiry_no) using a cyclic
> sequence,
> a company no.(1-8) and a nine digit sequential sequence number
>
> ie for company 4 on machine no. 2
>
> if the Alpha No. 2 sequence was 74
>
> Algorithm would use 744000000001 to produced a mod31 key of W19GEED1
>
> Alpha No. 1 - Sequences 63-69
> Alpha No. 2 - Sequences 70-76
> Alpha No. 3 - Sequences 77-83
>
> So Key ranges per machine would be 1. PXZFLJ9P - TDKMW3NR
> 2. TFVHGG4V - WYFPR1HW
> 3. X0PKCDZZ - ZGBSKZD0
>
> 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) ?
>
> Please put me straight, I'm totally confused.
>
> Thanks
>
> Ian (U.K)
>
> --
> to return e-mail remove nospam from address......
Received on Mon Jan 11 1999 - 22:23:29 CST