| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ENABLE NOVALIDATE behaviour bug
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 :)
Cheers
Richard
Richard,
Is this true for foreign key constraints as well, or does the optimizer use them for plan generation?
Jason.
-----Original Message-----
From: Richard Foote [mailto:richard.foote_at_bigpond.com]
Sent: Tuesday, June 29, 2004 6:26 AM
To: oracle-l_at_freelists.org
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.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Wed Jun 30 2004 - 08:01:19 CDT
-----------------------------------------------------------------
 |
 |