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@xxxxxxxxxxx> 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>
>

Other related posts: