This discussion is archived
1 Reply Latest reply: Dec 19, 2013 2:05 AM by Solomon Yakobson RSS

index cant be local if we go for composite partition is it true?

supersen Newbie
Currently Being Moderated

Hi,

 

I have created a partitioned table  TEMP as below

create  table temp( id number, date1  date) partition by range (date1) subpartition by id store 8;

 

Issue comes when i try to add primary key.  the above table  has more than 170 million records.

 

Adding primary key takes me more than  4 hours. 

 

So i decided to add primay key first with disable option and create unique index on it.

 

ALTER TABLE "temp" ADD CONSTRAINT "ID_PK"

PRIMARY KEY ("ID") DISABLE

---created in 1 seconds

WHEN i try to add unique index for this

CREATE UNIQUE INDEX id_pk ON temp

    (

id

    ) 

LOCAL PARALLEL 8 NOLOGGING TABLESPACE data

i got error as below

 

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE

index

 

 

Please tell me  if we are going for composite partition as above index cant be created local?

 

Please suggest

 

Thanks

Senthil.V

  • 1. Re: index cant be local if we go for composite partition is it true?
    Solomon Yakobson Guru
    Currently Being Moderated

    This is true for any unique index for any type of partitioning. What mean local index? It means each index partition is associated with a different table partition, so that all keys in an index partition refer only to rows stored in a single table partition. In such case the only way we can guarantee uniqueness across partitions (remember unique index means there is only one row with such index key, except NULL, in whole table) is having partitioning columns as part of index key.

     

    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points