CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Rupesh
Rupesh

Reputation: 95

Why Would someone Enable Constraint with NoValidate later Validate in Oracle

I was going thru a piece of Oracle PL-SQL code written by a programmer in the project which disables a Constraint and then while enabling first "Enables with novalidate" and then immediately "Validates" it. I got stumbled in understanding the reasoning behind below block:

        EXECUTE IMMEDIATE
               'ALTER TABLE '
            || i_table_name
            || ' ENABLE NOVALIDATE CONSTRAINT '
            || i_constraint_name;

        EXECUTE IMMEDIATE
               'ALTER TABLE '
            || i_table_name
            || ' ENABLE VALIDATE CONSTRAINT '
            || i_constraint_name;

What is wrong in just having above written in a single statement? Ex:"ALTER TABLE EMP ENABLE EMP_PK"

Please clear this doubt.

Upvotes: 2

Views: 3953

Answers (3)

D. Lohrsträter
D. Lohrsträter

Reputation: 388

The most important reason is, that "CREATE CONSTRAINT ENABLE VALIDATE" in one step requires a shared lock on the table during creation. But for large tables, the validation part requires often long time. Oracle must ensure, that even if the constraint is still not implemented, that during validation, no new violating data is inserted or modified.

This might have severe impact on processing with many locks.

On other hand, validating an ENABLED but NOT VALIDATED index, requires no lock, because new data is already checked against the constraint and all historic data requires just read consistency to validate. Of course, a short lock at the end is required to finally enable the constraint.

It is at least recommended to have check-constraints validated, because it can be important to help the optimizer. E.g. if a NOT NULL constraint is validated, the optimizer can trust on it and choose any matching index for this column. If the constraint is not validated, some records having NULL could exist and so not covered by any Index. Thus, a full table scan could be the result.

Upvotes: 0

marcmagransdeabril
marcmagransdeabril

Reputation: 1494

On the practical side, you could face the situation where due to some data issue (e.g. a sort of legacy data migration issue) the legacy/old data does not validate, just the new one.

In this case, you want to enable the constraints but to set them to NOVALIDATE.

This enables the constraints for the future data, but it does not validate the past failures (already in the DB).

Of course, this should be a temporary measure, as eventually you would like to fix the data issues and then enable the constraint with VALIDATE to avoid similar problems in the future.

Upvotes: 0

krokodilko
krokodilko

Reputation: 36097

Oracle recommends this order in the documentation

Efficient Use of Integrity Constraints: A Procedure

Using integrity constraint states in the following order can ensure the best benefits:

  • Disable state.
  • Perform the operation (load, export, import).
  • Enable novalidate state.
  • Enable state.

Some benefits of using constraints in this order are:

  • No locks are held.
  • All constraints can go to enable state concurrently.
  • Constraint enabling is done in parallel.
  • Concurrent activity on table is permitted.

Upvotes: 2

Related Questions