ENABLE NOVALIDATE behaviour bug 2004-06-29 - By Richard Foote
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@(protected) >
To: <oracle-l@(protected) >
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
