SQL Performance (MOSC)
MOSC Banner

How to speed up constraint creation?

I know the following is DDL as opposed to DML, but I would appreciate any help.

ALTER TABLE VH_TEST

ADD CONSTRAINT "CK_VHTEST01" CHECK (deletedFlag=(1) OR deletedFlag=(0))

ADD CONSTRAINT "PK_VHTEST" PRIMARY KEY ("ID")

ADD CONSTRAINT "FK_VHTEST41" FOREIGN KEY ("BILLINGCODEID") REFERENCES "ENCOUNTERBILLINGCODE" ("ID")

ADD CONSTRAINT "FK_VHTEST42" FOREIGN KEY ("CONFIDENTIALITYCODEID") REFERENCES "LK_CONFIDENTIALITY" ("ID")

...

There are a total of 34 foreign key constraints in the entire statement in addition to the check and primary key. The statement takes 20 hours 18 minutes to execute.

I found an article where I can create the check and foreign key constraints with the ENABLE NOVALIDATE clause. Then, use the ALTER TABLE MODIFY CONSTRAINT VALIDATE statement after changing the degree of parallelism on the table. This is supposed to allow the checking of the existing data using parallel processes.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center