AliD wrote:This is what i though initially but not going to happen.
AFAIK once the index is there, creating the primary key should be instant.
The index already forces the validation of the data. I assume the said index is created on the PK columns.Yes unique index is created on key which i suppose to be primary key
Also give bitmap index a try. It could surprise you with its size and performance.The columns which i suppose to add is having hugh numbers of distinct rows, so i dont think this would be feasible to create bitmap
It seems second step would validate each and every row in table for adding primary key constraint.
Only if the column(s) are nullable that comprise the primary key.
If the columns are NOT NULL then no validation is required once the unique index has been created.OK, the column was not defined as NOT NULL, so maybe thats why upon adding a constraint on existing local partition index its checking for NULL values.
Bitmap indexes cannot be used to enforce primary key constraints.Aware of that
NOTE: Performance of the partitioned table may be affected since you will now have a global index that did not exist before. Make sure you have taken that into account.I didn't create GLOBAL index. I create a unique partition index using LOCAL keyword which means its a local partitioned index.
That is because for any DML that affects the primary key (primarily INSERT/DELETE) the global index has to be updated. This includes loading of partitions using INSERT or using partition exchange. Such loads cannot use direct-path.Same as above
The only way that can be avoided is if the primary key columns are included in the partition key. Since you have an existing partitioned table that may not be the case.Yes my partition key is in leading column of primary key.