Re: ENABLE NOVALIDATE behaviour bug
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 29 Jun 2004 12:36:57 +0300
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
