Oracle11g新特性 - 快速在线新增not null字段

问题：某电信系统业务，其中有一张表有上亿条信息记录，而且很重要；要求在不停库和不锁表的情况下；对该表增加一个新的字段，如何实现。
在11g以前，在表中新增一个NOT NULL字段是件十分痛苦的事情，尤其是在表很大的情况，不但执行速度慢（会产生DDL锁，影响其它用户操作），而且由于数据长度的增加，很容易造成表中大量的行链接情况，影响性能。
在11g中，这种情况得到了彻底的改善， Oracle通过在数据字典 （ecol$） 中记录DEFAULT值，避免了繁重的更新操作，增加非空字段的时间和增加一个可空字段的时间完全一样。
下面先看10g中的情况：
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL> create table test as select * from dba_objects;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
     24871
SQL> set timing on
SQL> alter table test add new_col varchar(1000) default 'LARGE COLUMN' not null;

Table altered.

Elapsed: 00:00:20.34

可以看到即使对于只有2万多条数据的表，用了20秒，而且在这20秒期间，该表上有DDL锁，所有在该表的DML操作都会被阻塞。

注意，这里新增的列是非空的，如果没有非空这个限制，在10g中也是很快的：

SQL> alter table test add new_col2 varchar(1000);

Table altered.

Elapsed: 00:00:00.59

下面再看11g中的情况：
SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> create table test as select * from dba_objects;

Table created.

SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     13617

SQL> set timing on
SQL> alter table test add new_col varchar(1000) default 'LARGE COLUMN' not null;

Table altered.

Elapsed: 00:00:01.34

可以看到在11g中只用了1秒， 那么Oracle是 采用何种方法实现了如此大的性能提升呢？说起来也简单，在Oracle11g中，当添加一个包含DEFAULT值的NOT NULL字段，Oracle不会去更新现有的数据，仅仅是将默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。当 Oracle 在读取数据时，如果发现某一列为非空，但是实际存储却为空，就会从ECOL$中读取该列的默认值。

这样做的好处是不但减少了执行时间，而且不会产生DDL锁，进而不会影响其它用户的操作。

============

回到最开始的问题，如果数据库版本不是11g，那该怎么办呢，其实可以先新建一个允许空的字段，然后把该字段修改会非空，在对之前的数据进行update操作：
1. 新建一个允许空的字段。
SQL> alter table test add new_col varchar(1000);

Table altered.

Elapsed: 00:00:00.14
2. 修改该字段为非空，该语句只对之后的记录起作用，原有记录的该列还是为空值，因此该操作也很快。
SQL> alter table test modify new_col varchar(1000) default 'LARGE COLUMN';

Table altered.

Elapsed: 00:00:00.48
3. 更新原有的字段为默认值。
SQL> alter session enable parallel dml;
SQL> update /*+ parallel(t 8) */ test set new_col='LARGE COLUMN' where new_col is null;

24871 rows updated.
SQL> alter session disable parallel dml;

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

转载于:http://blog.itpub.net/26277071/viewspace-714184/