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

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:

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:

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:

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.

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.

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.

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.