Oracle primary key creation in parallel

After an earlier post, Saving a LOT of time with Oracle foreign key creation in PARALLEL, I was asked about primary key creation in parallel. It suffers from the same issue, and can be really time-consuming if you need to drop and recreate a primary key, either after a new load, or if you are partitioning an existing primary key.

There are 2 similar but slightly different approaches that I have tried and used. The first, more straightforward approach is to create a unique index on the column you wish, in parallel, and then reference that index in the primary key creation clause, with USING INDEX. The second involves creating the primary key in a disabled state, creating an index in parallel using whatever definition you want, and using the same name as the constraint, then enabling the constraint. In both cases, you need to turn off the PARALLEL option with an alter index statement, or you will get behavior you might not want.

A simple example of the less obvious second option follows:

alter table my_foo drop constraint pk_my_foo;
alter table my_foo add constraint pk_my_foo primary key(id) disable;
create unique index pk_my_foo on my_foo(id) global partition by hash(id) partitions 8 parallel;
alter index pk_my_foo noparallel;
alter table my_foo enable primary key;

Why would you want to do this instead of the more obvious approach of creating a stand alone index? Well, for one thing, you can keep the names the same, which is pretty cool. This can help keep standards in line, if you normally have a 1-1 match between the constraint name and the index name.