If you want to save time during a Data Pump import, you can transform constraints to NOT VALIDATED. Regardless of the constraint state in the source database, Data Pump will create the constraint using the novalidate keyword.

This can dramatically reduce the time it takes to import. But be aware of the drawbacks.

The Problem

Here is an example from a big import:

01-JAN-24 08:19:00.257: W-38 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
01-JAN-24 18:39:54.225: W-122      Completed 767 CONSTRAINT objects in 37253 seconds
01-JAN-24 18:39:54.225: W-122      Completed by worker 1 767 CONSTRAINT objects in 37253 seconds

There is only one worker processing constraints, and it took more than 10 hours to add 767 constraints. Ouch!

A Word About Constraints

Luckily, most databases use constraints extensively to enforce data quality. A constraint can be:

• VALIDATED
  • All data in the table obeys the constraint.
  • The database guarantees that data is good.
• NOT VALIDATED
  • All data in the table may or may not obey the constraint.
  • The database does not know if the data is good.

When you create a new constraint using the VALIDATE keyword (which is also the default), the database recursively full scans the entire table to ensure existing data is good. If you add more constraints, the database full scans each time. Since full table scans rarely make it into the buffer cache, each new constraint causes a lot of physical reads.

How Does Data Pump Add Constraints

Data Pump adds the constraints in the same state as in the source. As mentioned above, the constraints are most likely VALIDATED.

During import, Data Pump:

1. Creates an empty table
2. Loads data
3. Adds dependent objects, like constraints

It could look like this in a simplified manner:

For each of the alter table ... add constraint commands will trigger a full table scan because of the validate keyword. For a large database, this really hurts, especially because the full scan does not go parallel.

The Solution

The idea is to add the constraints as NOT VALIDATED but still ENABLED.

• NOT VALIDATED means the database doesn’t check the existing data
• ENABLED means the database enforces the constraints for new data

In Data Pump, there is a simple transformation:

impdp ... transform=constraint_novalidate:y

Data Pump adds all constraints using the novalidate keyword regardless of the state in the source database.

Instead of a full table scan for each new constraint, the alter table ... add constraint command is instant. It’s just a short write to the data dictionary, and that’s it. No full table scan.

This transformation requires Oracle Database 19c, Release Update 23 with the Data Pump Bundle Patch.

Update: A few ran into the following error when using the feature:

ORA-39001: invalid argument value
ORA-39042: invalid transform name CONSTRAINT_NOVALIDATE

Unfortunately, you need to add patch 37280692 as well. It’s included in 19.27.0 Data Pump Bundle Patch.

Is It Safe To Use?

Yes. There is no chance that this feature corrupts your data. Further, you know that data was good in the source, so it will be good in the target database as well.

However, you should take care when you are changing data on import. The alteration might lead to constraints being unable to validate and you won’t know this until you eventually perform the validation. The data is still perfectly fine, however, the constraint would need to be altered to match the new data.

Imagine the following:

• You are importing into a different character set – from singlebyte to Unicode.
• One of your constraints checks the length of a text using byte semantics with the function LENGTHB.
• After import into the Unicode database, some characters may take up two bytes or more.
• The result of the LENGTHB function would change and you would need to update the constraint definition. Either by allowing more bytes or using LENGTH or LENGTHC.

Let me give you an example:

• In my singlebyte database (WE8MSWIN1252), I have a table with these two rows:
  • ABC
  • ÆØÅ (these are special Danish characters)
• In singlebyte all characters take up one byte, so
  • LENGTHB('ABC') = 3
  • LENGTHB('ÆØÅ') = 3
• I migrate to Unicode and now the special Danish character expand. They take up more space in AL32UTF8:
  • LENGTHB('ABC') = 3
  • LENGTHB('ÆØÅ') = 6
• If I have a check constraint using the LENGTHB function, I would need to take this into accout. Plus, there are other similar functions that works on bytes instead of chars, like SUBSTRB.

It’s probably rare to see check constraints using byte semantic functions, like LENGTHB and SUBSTRB. But I’ve seen that in some systems that had to integrate with other systems.

You can end up in a similar situation if you:

• use the remap_data option to change data
• perform other kinds of data transformation

Since the constraint is still enabled, the database still enforces the constraint for new data after the import.

What’s the Catch?

Validated constraints are very useful to the database because it enables the optimizer to perform query rewrite and potentially improve query performance. Also, index access method might become available instead of full table scans with a validated constraint.

You want to get those constraints validated. But you don’t have to do it during the import. Validating an enabled, not validated constraint does not require a lock on the table. Thus, you can postpone the validation to a later time in your maintenance window, and you can perform other activities at the same time (like backup). Perhaps you can validate constraints while users are testing the database. Or wait until the next maintenance window.

Further, Data Pump always adds validated constraints in these circumstances:

• On DEFAULT ON NULL columns
• Used by a reference partitioned table
• Used by a reference partitioned child table
• Table with Primary key OID
• Used as clustering key on a clustered table

What About Rely

After import, you could manually add the rely clause:

alter table ... modify constraint ... rely;

Rely tells the database that you know the data is good. The optimizer still doesn’t trust you until you set the parameter QUERY_REWRITE_INTEGRITY to TRUSTED. Now, the optimizer can now benefit from some query rewrite options, but not all of them.

Nothing beats a truly validated constraint!

Validate Constraints Using Parallel Query

Since you want to validate the constraints, Connor McDonald made a video showing you can do that efficiently using parallel query:

Changing the default parallel degree (as shown in the video) might be dangerous in a running system.

• All other queries will also run with parallel
• ALTER TABLE might lead to cursor invalidation

So, here’s a better approach (thanks Connor):

alter session force parallel query;
alter table ... modify constraint ... enable validate;

• The validation happens:
• Without table lock
• In parallel
• And with no cursor invalidation

Nice!

Final Words

If you’re short on time, consider adding constraints as not validated.

The above case with more than 10 hours spent on adding validated constraints; that could have been just a few seconds with novalidate constraints. That’s a huge difference to a time critical migration.

Don’t forget to validate them at one point, because validated constraints are a benefit to the database.

Check my previous blog post for further details on constraint internals.

Appendix

Autonomous Database

The fix is also available in Autonomous Database, both 19c and 23ai.

Zero Downtime Migration

If you import via Zero Downtime Migration (ZDM) you need to add the following to your ZDM response file:

DATAPUMPSETTINGS_METADATATRANSFORMS-1=name:CONSTRAINT_NOVALIDATE,value:1

You might have to change METADATATRANSFORMS-1 to METADATATRANSFORMS-<n> if you have additional transformations (where <n> is a incrementing number).