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
✉vahidusefzadeh@gmail.com
Telegram channel :https://t.me/oracledb
Top comments (0)