Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ENABLE NOVALIDATE behaviour bug

Re: ENABLE NOVALIDATE behaviour bug

From: Richard Foote <>
Date: Wed, 30 Jun 2004 23:11:25 +1000
Message-ID: <029d01c45ea3$bf5bc840$0100000a@FOOTE>

Hi Jason,

The story's a little different between check constraints (including not null) and FK constraints.

With a check constraint that's been validated, Oracle can be *certain* of the data boundaries. So for example a check constraint that limits the data to be only be A,B or C provides the optimizer with useful information if you happen to be searching for data that has a value of Z.

With FKs, the story's somewhat different because effectively you're telling Oracle that a value of a column must be a value from a PK/Unique key somewhere (or indeed a null). How can Oracle be *certain* that a specific FK value can't possibly exist ? Well it can't really until it goes in and actually has a look. And the best way for Oracle to determine how to have a look is by looking at the statistics for the table/column as the CBO ordinarily does.

I've run tests on 8i and 9i and the CBO is not clever enough to determine such "certainties" with FKs even if there's a check constraint on the parent column(s), such as a check range constraints so that logically a FK value can't exist (I can post some demos if anyone's interested although it's easy enough to play with this oneself).

There are various oddities with all this that can sometimes change between releases. With 9.2 for example, with a standard check constraint, Oracle can determine the null data set without having to access any LIOs. With a not null constraint, Oracle needs to use an associated index to "access" the null data set.

Hope it makes some kinda sense :)




  Is this true for foreign key constraints as well, or does the optimizer use them for plan generation?


-----Original Message-----

From: Richard Foote [] Sent: Tuesday, June 29, 2004 6:26 AM
Subject: Re: ENABLE NOVALIDATE behaviour bug

Hi Mark,

Just to expand a little on a point made by Tanel. It can be a little "dangerous" to enable a constraint with novalidate. By doing so, you're effectively telling Oracle the data is valid, honest, whist the optimizer takes the attitude, "actually, I really don't believe you". This means that possibly useful constraint data can't be used by the optimizer when determining the best plan.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Received on Wed Jun 30 2004 - 08:01:19 CDT

Original text of this message