RE: ENABLE NOVALIDATE behaviour bug

The "quality" of the data could also be determined by the CBO by gathering
statistics with histograms to determine the amount of skew in the data,
right?  

I'm going to perform some tests to verify that, but this is a very
interesting and timely topic for me.

Thanks!
Jason.

-----Original Message-----
From: Richard Foote [mailto:richard.foote@xxxxxxxxxxx] 
Sent: Wednesday, June 30, 2004 7:11 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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

----- Original Message -----
From: "Looney, Jason" <Jason.Looney@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, June 30, 2004 8:40 AM
Subject: RE: ENABLE NOVALIDATE behaviour bug


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@xxxxxxxxxxx]
Sent: Tuesday, June 29, 2004 6:26 AM
To: oracle-l@xxxxxxxxxxxxx
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: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: