Re: Primary key quandary

"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? Received on Tue Jul 30 2002 - 10:31:27 CDT