jump to navigation

NOVALIDATE Constraints Part II – Does It Matter ? July 30, 2008

Posted by Richard Foote in Constraints, Novalidate Constraints, Oracle Cost Based Optimizer, Oracle Indexes, Primary Key, Unique Indexes.

As promised, more on NOVALIDATE constraints.

As previously discussed, a Primary Key or a Unique Key constraint that’s not validated is by default policed by a Unique index regardless. If there are no duplicate values, then no worries (yes, I’m Australian), Oracle will create the Unique index and enable the constraint in a NOVALIDATE state. If there are duplicate values, Oracle will complain about creating the Unique Index to police the constraint and you must either explicitly create a Non-Unique index when creating the constraint or use an existing Non-Unique index.

So what are the implications, if any, of having a Primary key constraint in a NOVALIDATE state, especially if a Unqiue index is used to police the constraint ? The data must really be unique else the Unique Index could not have been created, right ? Also Oracle can take advantage of all the benefits associated with having a Unique index such as less consistent reads and latching overheads as previously discussed.

Following on from the demo in Part I, if we have a table with a Primary Key in a NOVALIDATE state, policed by a Unique Index:


Table altered.

SQL> SELECT constraint_name, validated, uniqueness
FROM user_constraints c, user_indexes i
WHERE c.constraint_name = i.index_name AND c.table_name= ‘ZIGGY’;

--------------- ------------- ---------

Oracle will be able to use the Unique index to perform a nice, efficient, low latching Unique Scan with the index:

SQL> SELECT * FROM ziggy WHERE id = 42;

|Id|Operation                   |Name    |
| 0|SELECT STATEMENT            |        |

Everything’s perfect regardless of the PK constraint not being validated, right ?

Well, not exactly.

Remember, a PK constraint requires the data to be Unique AND Not Null. Now the Unique Index guarantees the data is indeed unique but it does nothing to protect us from having possible NULL values in our data. The index will simply ignore and not index any index entries that are fully NULL, therefore the PK column(s) could potentially, just maybe, contain NULLS. Brian Tkatch in a comment in Part I has a nice example of how this is possible.

This mean Oracle can not guarantee the index has index entries for every row in the table as any rows with a NULL PK will not be indexed. This can have serious reprecussions for the CBO when deciding an optimal execution plan.

For example, a query such as the following COUNT(*) query which could potentially be serviced via a “smaller” PK index segment can not use the Unique index and is forced to use either another index or a Full Table Scan:

SQL> select count(*) from ziggy;

| Id| Operation          | Name |
|  0| SELECT STATEMENT   |      |
|  1|  SORT AGGREGATE    |      |

Another example, this query with an ORDER BY clause could potentially use the Unique index to retrieve the data and so avoid the sort operation as the Clustering Factor of the index is very good. However, it can’t as again, the CBO can’t guarantee all data will be retrieved via the index:

SQL> select * from ziggy order by id;

10000 rows selected.

| Id| Operation          | Name |
|  0| SELECT STATEMENT   |      |
|  1|  SORT ORDER BY     |      |

However, if only we just validate the constraint, everything changes:


Table altered.

The COUNT(*) query suddenly starts using the index as a cheaper alternative as now, there can’t be any null values and so the index must reference all possible rows:

SQL> select count(*) from ziggy;

|Id|Operation             | Name    |
| 0|SELECT STATEMENT      |         |
| 1| SORT AGGREGATE       |         |

The ORDER BY query suddenly starts using the index and avoids performing the sort operation as again, the index will now guarantee all rows are returned in a sorted order:

SQL> select * from ziggy order by id;

10000 rows selected.

|Id|Operation                   |Name    |
| 0|SELECT STATEMENT            |        |
| 2|  INDEX FULL SCAN           |ZIGGY_PK|

The moral of the story. Provide the CBO with as much information as possible, as it can potentially use the information to determine a more optimal execution plan. Having a NOVALIDATE constraint possibly hides valuable information from the CBO and so needs to be used with caution.


1. Lars - July 30, 2008

Hi Richard,
nice discussion of this very specific topic.
Anyhow – usually the columns for a primary key are also marked as NOT NULL. Thus the CBO won’t loose any information at all.

Unfortunately no major DBMS vendor yet decided to make this the default setting for new columns….

best regards,

2. Mdu - July 30, 2008

Hi Richard,
Thanks for the useful stuff.
One question: is “primary key” the same as “unique key plus not null”? That is, can we use unique key plus not null, instead of primary key?

3. Asif Momen - July 30, 2008


Definitely yes, but you may not be able to apply Foreign key constraints when “Unique Key plus not null” is used.

4. Richard Foote - July 31, 2008

Hi Lars

Yes, PK columns are marked as NOT NULL but the point is if the PK has not been validated, then Oracle has no way of knowing whether or not the PK actually contains no nulls. The PK still marked as NOT NULL but indeed the CBO loses valuable information because it can’t guarantee there are no nulls as any existing data prior to the constraint being created could contain nulls. Hence the problem with novalidated constraints.

5. Richard Foote - July 31, 2008

Hi Mdu

Not the same, just equivalent. A table can only have the one PK but can have several unique constraints. I strongly recommend the use of PKs only because it meets most logical database models.

6. Richard Foote - July 31, 2008

Hi Asif

A FK can be applied to a parent Unique constraint just as it can with a PK constraint. The child record is only concerned with there being an associated parent record which it can guarantee with either a PK or UK constraint.

Note though that with a novalidate PK or UK constraint, there could actually be more than one associated parent record as there could be duplicate records but Oracle will create the corresponding FK child record regardless. You can subsequently deleted the duplicate parent records so long as there’s still at least one record left that the FK can reference.

7. B. Polarski - July 31, 2008

I just wonder why Oracle ever allowed something to be called PK while contains NULL.

8. Richard Foote - July 31, 2008

Hi B. Polarski

Well, don’t forget, by default Oracle doesn’t allow such a thing. It’s only possible if you explicitly state you want the constraint to be NOVALIDATE or DEFERRABLE. A validated, non-deferrable PK constraint can not contain a null value. It’s your choice.

Why does Oracle allow such a thing ?

As mentioned in the post, NOVALIDATE is useful if you have data that violates the constraint (such as some nulls in the PK) but you want to quickly put on the constraint to prevent further violations to the data and you intend to subsequently clean up the data. It’s a special case.

DEFERRABLE is useful if you want to temporarily violate a constraint within a transaction but for it to be clean at the time of the COMMIT. Again, it’s a special and somewhat rare case.

Hope it makes sense.

9. Mdu - July 31, 2008

Hi Asif/Richard,
Thanks for replies.

10. Brian Tkatch - July 31, 2008

Note that PRIMARY KEY is a default used by other statements. For example, REFERENCES by default uses the PK. MATERIALIZED VIEW LOG has an option for PK, and so on.

11. Constraints and how they may affect cost based optimizer’s choises « H.Tonguç Yılmaz - Oracle Blog - March 5, 2009

[…] Having a Novalidate constraint hides valuable information from the CBO […]

12. Mags - December 5, 2009

I have a unique issue. I have a Foreign Key constraint that is in Deferrable ENABLED NOT VALIDATEd mode, referencing a enabled validated PK constraint of another table.

At the same time I also have a Praimary key defined on the same column on which FK constraint is defined.

When I analyze the underlying table or index I feel that the CBO plan changes and it is fixed only when Index is REBUILD.

Can you explain in detail the reason on this.

Richard Foote - December 6, 2009

Ummm, no.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: