Re: ENABLE NOVALIDATE behaviour bug

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Tue, 29 Jun 2004 12:36:57 +0300
Message-ID: <003901c45dbc$9fa2d4e0$ad869fd9@porgand>

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

Received on Tue Jun 29 2004 - 04:33:42 CDT