Ask The Real Tom

July 28, 2010



Enable and Validate Constraints in Parallel (Oracle)

Enabling and validating constraints is can take a huge amount of time in big oracle databases.
To speed the validaten up, it can be done in parallel.
This can save you valuable time in release windows.

  1. Enable Constraint with novalidate option
  2. Set parallel degree on the table or on session with force ddl parallel
  3. Enable nable the constraints with validation

Example:

ALTER TABLE T_1 ADD CONSTRAINT NC_1 not null (colname) disabled;
ALTER TABLE T_1 ENABLE NOVALIDATE CONSTRAINT NC_1;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
ALTER TABLE T_1 PARALLEL 8;
ALTER TABLE T_1 ENABLE VALIDATE CONSTRAINT NC_1;
ALTER SESSION DISABLE PARALLEL DDL;

1 Comment »

  1. and with that example you will leave table with parallel option which is not necessary what you want as some queries might start using it (and it’s not always good, especially for oltp queries) ;p

    Comment by asdf — February 26, 2015 @ 10:05 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress