September 20, 2012

Minimize the time it takes to enable referential constraints by doing it in parallel

Alter session to "FORCE PARALLEL DDL"

The table in this example is created with the NOPARALLEL attribute. Using ALTER SESSION FORCE PARALLEL DDL, the validation of the constraint is done in parallel. This cuts the time consumed to under one minute in my environment.

-- Disable the constraint (only takes a second)
ALTER TABLE usr.Table1 DISABLE CONSTRAINT TABLE1_FK_COL1;

-- Enable constraint in parallel takes about 40 seconds (NOPARALLEL takes approx. 5 minutes)
ALTER TABLE usr.Table1 ENABLE NOVALIDATE CONSTRAINT TABLE1_FK_COL1 ;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
ALTER TABLE usr.Table1 ENABLE VALIDATE CONSTRAINT TABLE1_FK_COL1 ;
ALTER SESSION ENABLE PARALLEL DDL;


What the documentation says about ALTER SESSION ... PARALLEL

The PARALLEL parameter determines whether all subsequent DML, DDL, or query statements in the session will be considered for parallel execution. This clause enables you to override the degree of parallelism of tables during the current session without changing the tables themselves.

FORCE forces parallel execution of subsequent statements in the session. If no parallel clause or hint is specified, then a default degree of parallelism is used. This clause overrides any parallel_clause specified in subsequent statements in the session but is overridden by a parallel hint.

Specify ENABLE to execute subsequent statements in the session in parallel. This is the default for DDL and query statements.
  • DML: DML statements are executed in parallel mode if a parallel hint or a parallel clause is specified.
  • DDL: DDL statements are executed in parallel mode if a parallel clause is specified.
  • QUERY: Queries are executed in parallel mode if a parallel hint or a parallel clause is specified.

Read more about ALTER SESSION [ENABLE | DISABLE | FORCE] PARALLEL [DML | DDL | QUERY] in the Oracle documentation on the links below.

No comments:

Post a Comment