Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -

 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

Comments

Post a Comment

Popular posts from this blog

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more

Oracle 23ai — Track Table and Partition Scan Access

By -
Image
  In Oracle 23ai, a new view named   [dba|all|user]_table_access_stats   has been introduced, enabling users to view the access history of tables and partitions. This view shows how many times each table or partition has been scanned in each instance and the last time a table was accessed. SQL > create table jadval(id number, name varchar2( 100 ), last_name varchar2( 100 )); Table created. SQL > insert into jadval values ( 1 , 'payan' , 'rafat' ); 1 row created. SQL > select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME from user_table_access_stats; no rows selected SQL > select * from jadval; ID NAME LAST_NA ---------- ------- ------- 1 payan rafat SQL > select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME from user_table_access_stats; TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME ---------- --------------- ----------- --------...
Read more