Oracle 11g增加列，并带默认值的新特性

yingyifeng

最新看了Oracle 11g增加列，并带默认值的新特性，还是有很多疑问，SQL REFERENCE中写到
If you specify the DEFAULT clause for a NOT NULL column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.

而
If you specify the DEFAULT clause for a nullable column, then the default value is added to existing rows as part of this ALTER TABLE statement, and any update triggers defined on the table are fired. This behavior also results if you change a NOT NULL column with a default value to be nullable.

这是不是就是说，如果我有一张表：
select * from test2;
        ID
----------
         1
         2
         3

那么当我添加：
alter table test add(b number default 10 );


此时默认还是会更新所有的行数据


而
alter table test add(b number default 10 not null);
仅仅是讲10写到col$基表中之前的数据是不会更新的。


不知道理解是否有问题

Maclean Liu(刘相兵

"仅仅是讲10写到col$基表中之前的数据是不会更新的。"

差不多 ，但是不是写到col$ 基表 而是写到 11g 新加入的ecol$ 基表中

参考 http://t.askmaclean.com/thread-428-1-1.html

yingyifeng

在您说的那篇文章中我看到
In addition, the following ADD COLUMN operations can now run concurrently with DML operations:
* Add a NOT NULL column with a default value
* Add a nullable column without a default value
* Add a virtual column

那是不是就是说11g中如果我不添加not null 并且默认是default value 是不是说他虽然也会写到ecol$基表中，但是还是会将表中的所有数据更新，也就是该新特性没有体现了。

anbob

这个问题在那篇上传的trc中可以找到，如果是default 没有not null，会看到
PARSING IN CURSOR #3 len=28 dep=1 uid=0 oct=6 lid=0 tim=1330414710946491 hv=229214010 ad='6cd05fb0' sqlid='1rzf5g46um1tu'
update "TE1" set "JOB"='ta'
END OF STMT
如果default 有 not null trc中是没有update te1,而是
PARSING IN CURSOR #11 len=37 dep=1 uid=0 oct=2 lid=0 tim=1330415222140360 hv=4050124187 ad='6ccc7548' sqlid='cqrnq6vsqgzcv'
insert into ecol$ values (:1, :2, :3)
END OF STMT

Maclean Liu(刘相兵

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select count(*)  from large;

  COUNT(*)
----------
   3378944
   
   
SQL> set timing on;


SQL> alter table large add name varchar2(30) default 'n' not null;

表已更改。

已用时间:  00: 00: 00.46

NOT NULL + DEFAULT  + 11g ADD COLUMN DEFAULT VALUE NEW FEATURE

特性被使用， 迅速完整


SQL> alter table large add name1 varchar2(30) default 'n' ;

表已更改。

已用时间:  00: 03: 02.03


NULLABLE +  DEFAULT +  11g ADD COLUMN DEFAULT VALUE NEW FEATURE

特性未被使用， 很慢

Maclean Liu(刘相兵

另外需要注意的是 11g R1 add default column 对于default sysdate 存在bug ，在11gR2中得到修复

Add Date Column To Table With Default Sysdate And Not Null Inserts Zeros

Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.6
This problem can occur on any platform.
Symptoms
Adding column of date datatype to a table, with default value sysdate and not null inserts zeros instead of sysdate

example of the problem shown below:

SQL> create table test (anumber number);
.
Table created.
.
SQL> insert into test values (1);
.
1 row created.
.
SQL> alter table test add (adate date default sysdate not null);
.
Table altered.
.
SQL> select * from test;
.
ANUMBER ADATE
---------- ---------
1 00-DECEMB

Cause
The cause was identified as unpublished Bug 7028779
Solution
The Bug will be Fixed in 11.2 , and probably the fix will be introduced in future patchsets of 11.1 as well

You may use the work around below in the mean time of adding the column on two steps:

SQL> create table test (anumber number);
.
Table created.
.
SQL> insert into test values (1);
.
1 row created.
.
SQL> alter table test add (adate date default sysdate);
.
Table altered.
.

SQL> alter table test modify (adate not null);
.
Table altered.
.
select * from test;
.
ANUMBER ADATE
---------- ---------
1 08-MAY-08