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.

A very simple example. We had a statement that required an "empty" set to be
returned and used a query to list all null values for a not null column.
Problem being the constraint was inadvertently enabled with novalidate after
the table was rebuilt meaning that a previous "efficient" plan was replaced
by a horrendously expensive and unnecessary FTS. Although it might sound
like an odd thing to do (and in this specific example, it was a rather odd
thing to do), it's not actually uncommon for queries to sometimes request
data that can't possibly exist due to a constraint. Only by having a
validated constraint can the optimizer "know" that such a query will return
no rows and act accordingly (or "know" that there are no nulls and use an
index etc ...).

Sometimes performing one scan to validate a constraint can save many
subsequent unnecessary scans !!

Cheers

Richard

----- Original Message -----
From: "Mark Richard" <mrichard@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 29, 2004 4:09 PM
Subject: ENABLE NOVALIDATE behaviour bug

Hi List,

I regularly have to change the structure of some fairly large tables (~200m
rows).  Often we use the opportunity to do a full table rebuild if we want
to change other settings (such as index locations) but other times we would
prefer to modify the existing table.

I currently have the scenario where I need to add a single CHAR(1) column
to a 250m row table and populate it with a constant value (new records may
have a different value).  The approach I am considering is:

1) alter table blah add (mycol char(1));
2) update blah set mycol = 'F';     {perhaps include a parallel hint on
this statement}
3) alter table blah modify mycol not null enable novalidate;

I was hoping to use "enable novalidate" to avoid a verification of all
records when I know they will be populated.


----------------------------------------------------------------
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: