11g 对一个400万条记录的表添加约束为not null的列
SQL>  select * from v$version;

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

Elapsed: 00:00:00.07

SQL> create table justin as select id,define_codes from coupon where rownum <=4000000;   

Table created.

SQL> select count(*) from justin;

  COUNT(*)
----------
   4000000

SQL> set timing on
SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.14
SQL> alter table justin add name varchar2(20) default 'N' not null;

Table altered.

Elapsed: 00:00:01.13
SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
只花费了1.13秒
而同样的情况 换成10g
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create table justin as select id,define_codes from coupon where rownum <=4000000;  

Table created.

SQL> select count(*) from justin;

  COUNT(*)
----------
   4000000

SQL> set timing on

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.09
SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.01
SQL> alter table justin add name varchar2(20) default 'N' not null;

Table altered.

Elapsed: 00:06:36.29
SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
这一步骤却需要6分多钟 速度十分缓慢
查看各自的10046文件，发现最大的区别是，10g的跟踪文件有以下语句
update "JUSTIN" set "NAME"='N'
而11g中却没有，不知11g是怎么将已有的400万条记录更新的，附上跟踪文件，请各位一起分析一下

11g.txt
(2011-02-14 14:56:21, Size: 45 KB, Downloads: 2)

10g.txt
(2011-02-14 14:56:21, Size: 460 KB, Downloads: 2)