Re: ENABLE NOVALIDATE behaviour bug

Hi!

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

You should use:

alter table t add c char(1) default 'F' not null;
alter table t modify c default null;

That way you would avoid the additional update and enable constraint
clauses.


> SQL> alter table blah modify mycol not null enable novalidate;
...
> This is happening on Oracle 8.1.7.4 (running on Solaris) and is
repeatable.
> Whenever "enable novalidate" is used the constraint doesn't appear.
Trying
> to insert null data shows the constraint is actually there, just not
> appearing in describe.

It's just the way DESC clause in sqlplus queries the cdef$ and ccol$ data
dictionary tables. It only shows validated not null constraints there,
probably for consistency with Oracle optimizer behaviour, which cannot take
a not validated constraint seriously when optimizing execution plans.

You can still see your constraint from dba_constraints, you just see it's
not validated from there.

Tanel.


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