When executing the IMPDP command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints:

ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50);  
ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN (5, 15));

We drop the MYTB table and recreate it using a dump file:

SQL> drop table mytb;
Table dropped.

W-1 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
W-1 . . imported "VAHID"."MYTB"                                  19 GB 337830912 rows in 83 seconds using external_table
W-1 Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
W-1      Completed 2 CONSTRAINT objects in 208 seconds

As shown, creating the two constraints takes approximately 3 minutes, while the table creation only takes 1 minute. These constraints are created in the ENABLED and VALIDATED states:

SQL> SELECT owner, constraint_name, status, validated FROM user_constraints;  
OWNER      CONSTRAINT      STATUS    VALIDATED  
---------- ----------      --------  -------------  
VAHID       CHECK1          ENABLED   VALIDATED  
VAHID       CHECK2          ENABLED   VALIDATED

From Oracle 23ai, a new option named constraint_novalidate has been introduced for the Transform parameter in the IMPDP command. This option allows constraints to be restored in the ENABLED NOT VALIDATED state:

TRANSFORM=constraint_novalidate:y

By using this, the speed of restoring constraints increases significantly, as the current table data is not validated. However, the constraint_novalidate option only bypasses data validation during constraint creation. After the IMPDP operation, these constraints will still apply to new data.

We repeated the operation using the TRANSFORM=constraint_novalidate:y parameter:

W-1 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA  
W-1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 82 seconds using external_table  
W-1 Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT  
W-1 Completed 2 CONSTRAINT objects in 1 second  
As shown, the table restoration still takes 82 seconds, but the constraints are created in just 1 second.

If you’re concerned about invalid data transferred from the source database to the target database after the import operation, it’s recommended to execute the VALIDATE command at a convenient time. This operation does not lock the table but will incur significant I/O activity.

In the example below, in Session 1, we lock the table with a row-exclusive lock. In Session 2, we perform a parallel VALIDATE operation. Both operations run concurrently, and the VALIDATE command does not lock the MYTB table:

Session 1:

SQL> DELETE VAHID.MYTB WHERE ROWNUM=1;  
1 row deleted.

Session 2:

SQL> ALTER SESSION FORCE PARALLEL QUERY;  
Session altered.  

SQL> ALTER TABLE VAHID.MYTB MODIFY CONSTRAINT CHECK1 VALIDATE;  
Table altered.  
Executed in 14.951 seconds.  

SQL> ALTER TABLE VAHID.MYTB MODIFY CONSTRAINT CHECK2 VALIDATE;  
Table altered.  
Executed in 15.448 seconds.

These two constraints are now in the ENABLED VALIDATED state:

SQL> SELECT owner, constraint_name, status, validated FROM user_constraints;  
OWNER      CONSTRAINT      STATUS    VALIDATED  
---------- ----------      --------  -------------  
VAHID       CHECK1          ENABLED   VALIDATED  
VAHID       CHECK2          ENABLED   VALIDATED

Vahid Yousefzadeh
Oracle Database Administrator
✉[email protected]
Telegram channel :https://t.me/oracledb