Saving a LOT of time with Oracle foreign key creation in PARALLEL

One of the things that can consume a ton of time when doing DB maintenance is recreation of foreign keys. You can't drop and recreate an index when a foreign key points to it. So, if you are dropping a unique index or primary key, and you get an error telling you that you can't do that, here are the steps to take:

First, find the foreign keys that point to the index. Let's say you're dropping the primary key on a table, perhaps to hash partition it. If the table (parent_table) has a column named id, you can query the USER_CONS_COLUMNS view with the table_name and column_name of the table you are maintaining. It will show you the name of the constraint (and owner). If this is a system generated constraint name, and that isn't enough to identify the source table, you can join that constraint name against user_constraints and get more info.

Example:
select * from user_cons_columns where table_name = 'PARENT_TABLE' and column_name = 'ID';

Once you find the foreign keys, you need to determine how to recreate them. Hopefully they are simple. In any event, you can drop the foreign keys, making notes so you can recreate them when you are done. After the drop and recreation of the index you are working on, you can then recreate the foreign key.

So, drop the foreign key causing the problem:
ALTER TABLE child_table DROP CONSTRAINT fk1_child_table;

Do your work on the parent table:
-- get rid of the old primary key
ALTER TABLE parent_table DROP CONSTRAINT pk_parent_table;

-- replace with a performant partitioned primary key
ALTER TABLE parent_table ADD CONSTRAINT pk_parent_table
PRIMARY KEY(id) USING INDEX GLOBAL PARTITION BY HASH(id) PARTITIONS 64
TABLESPACE tblspc1;

Time to add the foreign key back... But wait! If these are huge tables (a strong possibility if you are partitioning them or their indexes!) it can take a LONG time to recreate a foreign key. Most of that time is spent doing validation, and it happens serially. There is a really easy way to create that foreign key in a few steps and save yourself a tremendous amount of time!

I ran across this idea in a post here, so I can't claim credit for it, but I do use it in different scenarios which might lead others to find it. It was a great post, and I plan to use it a lot more.

It's very simple - you defer validation, which you CAN do in parallel. Simply recreate the foreign key, like so:

ALTER TABLE child_table ADD CONSTRAINT fk1_child_table FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE ENABLE NOVALIDATE;

Of course, now you have a foreign key that needs validation. Very simple, but we want to do it in parallel, so our multi-hundred gigabyte table can be done quicker. First, turn on PARALLEL DDL for the session:

-- we can't go faster if we don't turn on parallel!
ALTER SESSION ENABLE PARALLEL DDL;

-- temporarily make the child table parallel enabled:
ALTER TABLE child_table PARALLEL;

-- prepare to wait a while on this, but you can entertain yourself
-- by watching your validation go massively parallel in Enterprise Manager :-)
ALTER TABLE child_table MODIFY CONSTRAINT fk1_child_table VALIDATE;

-- once the validate FINALLY finishes, be sure to turn off the parallel option, unless
-- you know what you are doing and really, really wanted it on.
ALTER TABLE child_table NOPARALLEL;

I should have tried this on the other operations as well, but that's for next time!