oracle default not null,11g为表增添带有not null default约束的列的改进

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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-688366/，如需转载，请注明出处，否则将追究法律责任。