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

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:

alter table blah add (mycol char(1));

update blah set mycol = 'F'; {perhaps include a parallel hint on
this statement}

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_at_freelists.org
put 'unsubscribe' in the subject line.
--

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_at_freelists.org