Re: partitioned tables

If you make TXN_DATE_ID the first column in the PK index you would be able to make it a local prefixed index, so when you go to drop a partition it will just drop the index partition as well. If it's a global index Oracle owuld have to either updat ethe index on drop or it would render the index invalid and you'd need to rebuild.

Although, IIRC unique indexes can't be partitioned so you wouldn't be able to make it a PK index any way...

Finn

On Dec 11, 2007 4:51 PM, Joe Smith <joe_dba_at_hotmail.com> wrote:

> My primary key is a composite of 3 columns, but I range parition only on
> one column.
>
> It that a problem?
>
>
> I know it is a date field, but we are using a number for a date, i.e.
> yyyymmdd.
>
> Would I need to set up a partitioned tablespace for the index, i.e. PK?
>
> CREATE TABLE F_MBR_ACCT_TRANSACTION
> (
> MBR_ACCT_ID INTEGER NOT NULL ,
> TXN_DATE_ID INTEGER NOT NULL ,
> SPONSOR_TRAN_ID INTEGER NOT NULL ,
> TXN_DTTM DATE NULL ,
> TXN_AMOUNT NUMBER(14,2) NULL
> CONSTRAINT F_MBR_ACCT_TRANSACTION_PK PRIMARY KEY
> (MBR_ACCT_ID,TXN_DATE_ID,SPONSOR_TRAN_ID))
> partion by range (TXN_DATE_ID)
> (partition jan_2007 values less than (20070131) tablespace
> smart_part_jan_2007,
> partition feb_2007 values less than (20070228) tablespace
> smart_part_feb_2007,
> .
> .
> .
> partition jan_2007 values less than (20071231) tablespace
> smart_part_dec_2007);
>
>
>
> ------------------------------
> Share life as it happens with the new Windows Live. Share now!<http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_122007>
>

--
http://www.freelists.org/webpage/oracle-l