ENABLE NOVALIDATE behaviour bug 2004-06-29 - By Tanel Poder
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@(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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
