Re: partitioned tables
- From: "Finn Jorgensen" <finn.oracledba@xxxxxxxxx>
- To: joe_dba@xxxxxxxxxxx
- Date: Tue, 11 Dec 2007 21:41:01 -0500
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:
