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.