NOVALIDATE Constraints Part II – Does It Matter ?

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:

SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) ENABLE NOVALIDATE;

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’;

CONSTRAINT_NAME VALIDATED     UNIQUENES
--------------- ------------- ---------
ZIGGY_PK        NOT VALIDATED UNIQUE

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            |        |
| 1| TABLE ACCESS BY INDEX ROWID|ZIGGY   |
|*2|  INDEX UNIQUE SCAN         |ZIGGY_PK|
------------------------------------------

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    |      |
|  2|   TABLE ACCESS FULL| ZIGGY|
---------------------------------

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     |      |
|  2|   TABLE ACCESS FULL| ZIGGY|
---------------------------------

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

SQL> ALTER TABLE ziggy ENABLE VALIDATE PRIMARY KEY;

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       |         |
| 2|  INDEX FAST FULL SCAN| ZIGGY_PK|
-------------------------------------

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            |        |
| 1| TABLE ACCESS BY INDEX ROWID|ZIGGY   |
| 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.