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
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
Please tell me if we are going for composite partition as above index cant be created local?
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.