Re: Primary key quandary

From: Tweetie Pooh <tp601553_at_cia.gov>
Date: Wed, 31 Jul 2002 10:40:16 GMT
Message-ID: <Xns925C6C8EB4F58TweetiePooh@62.253.162.105>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> honoured comp.databases.oracle.server on Tue 30 Jul 2002 03:31:27p with news:3d46b14f$0$234$ed9e5944_at_reading.news.pipex.net:

> "Tweetie Pooh" <tp601553_at_cia.gov> wrote in message
> news:Xns925B956CA9F93TweetiePooh_at_62.253.162.105...

>> Our database is fairly small (for Oracle) and the main table has a mere
>> 26 million rows in it.
>>
>> This table is partitioned by a date but the primary key does not need
>> this date and is generated by the data feed.  Now what is the best way
>> to apply 

> a

>> primary key that will allow greatest flexibility?  (A bit ambiguous?!!)
>>
>> The data is static once added to the database and all attributes are
>> set 

> by

>> an external program  (ie it creates the data and user update on this

> external

>> app, once data is delted from the external app it is moved from a
>> buffer space to this database).  The data is partitioned by a date as
>> most 

> queries

>> will be on a single partition period and if we do delete data it will
>> be 

> for

>> a partition period at a time (rolling data set idea).  Queries will not

> use

>> the primary key except to link tables.
>>
>> The primary key does not have this date and so partitioning it is a bit
>> awkward.  To add the date means playing with foreign key constraints.

>
> I don't understand the problem.
>
> The following works for me.
>
> CREATE TABLE TEST
> ( ID NUMBER PRIMARY KEY,
> DATA VARCHAR2(20),
> DATESTAMP DATE)
> PARTITION BY RANGE (DATESTAMP)
> ( PARTITION sales_q1 VALUES LESS THAN (
> TO_DATE('01/04/2002','DD/MM/YYYY' )),
> PARTITION sales_q2 VALUES LESS THAN (
> TO_DATE('01/07/2002','DD/MM/YYYY' )),
> PARTITION sales_q3 VALUES LESS THAN (
> TO_DATE('01/10/2002','DD/MM/YYYY' )),
> PARTITION sales_q4 VALUES LESS THAN (
> TO_DATE('01/01/2003','DD/MM/YYYY' )))
> /
>
> In other words primary key of id, partitioned by a datastamp column.
> Whjy do you think the date column needs to be in the primary key?
>
>
>
>
>

It is to do with puting the PK into a partitioned index such that the partitions are "mirrored". You can do this with non PK indexes but you can with others using LOCAL keyword. Received on Wed Jul 31 2002 - 05:40:16 CDT