永久链接：http://www.ixdba.com/html/y2007/m08/159-oracle11g-add-column.html
老和尚在他的blog中描述了了这个问题，我这里做一个详细的测试来说明这个问题。
在oracle 11g以前，如果需要在一个表中执行类似如下的命令，而且这个表本身已经有很多数据，那么，这个操作将可能花费很长的时间，并且可能阻塞应用：
SQL>alter table table_name add field_name number default 0 not null;
因为这个操作需要修改以前所有的行，并把他们都修
改为默认值，如以上的0。但是，这个情况在oracle 11g中有了巨大的改变，oracle
11g中，如果对一个表增加一个列，并带有默认值，Oracle并没有真实的去修改以前的列，只不过通过查询的时候，采用类似NVL(null,新值)的
方法转换一下，让以前的列看起来象有值一样。
这个过程在select 的时候就转换好了，所以，对于使用者看来，他们是有值的，这个细小的改动将大大减少这个语句的执行时间，使得这样的语句在oracle 11g中不会引起任何阻塞，更不会影响性能了。
下面看一个简单的对比说明：
首先是Oracle 10g的测试
Piner@10gR2>create table test(a int);
Table created.
Piner@10gR2>begin
2 for i in 1..1000 loop
3 insert into test values('1');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
Piner@10gR2>set serveroutput on
Piner@10gR2>exec show_space('TEST');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................135641
Last Used Block.........................8
PL/SQL procedure successfully completed.
Piner@10gR2>alter table test add flag char(2000) default 1 not null;
Table altered.
Piner@10gR2>exec show_space('TEST');
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................134409
Last Used Block.........................128
PL/SQL procedure successfully completed.
Piner@10gR2>select count(*) from test where flag is null;
COUNT(*)
----------
0
可以看到的是，在增加默认值的列以后，该表的使用空间发生了巨大的变化，也证明数据库修改了以前的所有的块中的数据，让他们都生效。
以下是Oracle 11g的操作
Piner@11gR1>create table test(a int);
Table created.
Piner@11gR1>begin
2 for i in 1..1000 loop
3 insert into test values('1');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
Piner@11gR1>set serveroutput on
Piner@11gR1>exec show_space('TEST');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................83745
Last Used Block.........................8
PL/SQL procedure successfully completed.
Piner@11gR1>alter table test add flag char(2000) default 1 not null;
Table altered.
Piner@11gR1>exec show_space('TEST');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................83745
Last Used Block.........................8
PL/SQL procedure successfully completed.
Piner@11gR1>select count(*) from test where flag is null;
COUNT(*)
----------
0
这里可以看到，同样的记录，做了同样的操作，增加新列，并设置默认值以后，表的使用空间没有发生任何变化，也同样证明了Oracle没有修改以前的块中的数据。
这里的例子只有1000条记录，主要是为了体现了
空间的变化。如果是几是万条，或者几百万条以上的表，同样的操作，oracle 11g是马上就结束了，不会有任何影响，而oracle
10g可能需要非常长的时间，这个时间表是被锁住的，不能执行任何DML操作，对于写频繁的应用来说，将会导致应用的阻塞。
新添加的列，如果指定了默认值，并没有修改以前的块，读取以前的行的时候，可能发生一个转换过程。但是，如果对于已经设置过默认值的情况，插入新值的时候，这么默认值是怎么处理的呢：
之前的情况：
Piner@11gR1> desc test;
Name Type Nullable Default Comments
---- ---------- -------- ------- --------
A INTEGER Y
FLAG CHAR(2000) Y 0
Piner@11gR1>exec show_space('TEST');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................83745
Last Used Block.........................8
PL/SQL procedure successfully completed.
Piner@11gR1>select count(*) from test;
COUNT(*)
----------
1000
插入新记录：
Piner@11gR1>begin
2 for i in 1..1000 loop
3 insert into test(a) values('1');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
Piner@11gR1>exec show_space('TEST');
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................90889
Last Used Block.........................128
PL/SQL procedure successfully completed.
可以看到，新插入的1000条记录，块的个数发生了非常大的变化，证明了在新的记录中，Oracle实际上是真正把默认值添加到块中去了。但是，只有以前的记录依然是没有值的，需要靠转换来完成。
现在有2000条记录，是分2次插入进去的，这个时候，如果去掉默认值，会发生什么情况？是一半有值，一半没有值，还是都有值呢？我们再看：
Piner@11gR1>alter table test modify flag default null null;
Table altered.
Piner@11gR1>select count(*) from test where flag is null;
COUNT(*)
----------
0
Piner@11gR1>exec show_space('TEST');
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................90889
Last Used Block.........................128
PL/SQL procedure successfully completed.
可以看到，虽然解除了默认值，块的个数也没有任何变化，以前的记录依然是没有真实的写入任何数值到块中的，但是，Oracle 依然能把以前的默认值显示出来，因为is null查不出来任何记录，而且查询记录就可以看到实际的值就是以前的默认值。
最后，再增加1000条记录，这个时候可以发现，因为去掉了默认值，现在在另外一个列上，写入的为null。
Piner@11gR1>begin
2 for i in 1..1000 loop
3 insert into test(a) values(1);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
Piner@11gR1>exec show_space('TEST');
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................90889
Last Used Block.........................128
PL/SQL procedure successfully completed.
Piner@11gR1>select count(*) from test where flag is null;
COUNT(*)
----------
1000
所以，对于整个过程分析下来，可以这么认
为，oracle在add column
default的时候，只是记录了一个标记，标记为这个点之前的所有块，如果是NULL的话，是需要转换的，反之取出实际的值。而这个点之后的块，就是按
照实际的值来处理，如果中途反复修改，则以修改为准。
因为add column default，对于一个列，最多发生一次，所以，一个列只记录一个参照点即可。
但是，对于这个特性，有人想问，如果想保持以前的值为null，不要强行转换，但是，又想把新增加的列设置一个默认值，怎么办？其实也很简单，跟以前一样，分2步走即可，这样的话，以前的值还是null，新的值将为默认值。
Piner@11gR1>alter table table_name add field_name number;
Piner@11gR1>alter table table_name modify field_name default 0;
2081
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
