oracle 11g对大表中添加DEFAULT值的NOT NULL字段速度有大幅度的提升

今天同事问我一个问题他说在一张2000万的表上增加了一个字段并字段一个默认值，执行这条语句（alter table tablename add new_col default ‘col’）一个小时没有执行完，问我有没有其他解决方法

我查了一下资料发现

Oracle11g中，在添加一个包含DEFAULT值的NOT NULL字段，Oracle不会去更新现有的数据，Oracle需要做的不过是将默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。这张表利用BLOB字段存储ALTER TABLE添加的DEFAULT值

然后我测试一下情况（在11g中测试alter table tablename add new_col default ‘col’ 后代not null和不带not null的区别）

1.新建一张表test

SQL> create table test (id number,name varchar(10));

表已创建。

2.向test表中插入500000记录

SQL> begin

  2  for i in 1..500000 loop

  3  insert into test values(i,'jack');

  4  end loop;

  5  end;

  6  /

PL/SQL 过程已成功完成。

SQL> select count(1) from test;

  COUNT(1)

----------

    500000

SQL>

SQL> set timing on

3．向表中添加一个新的字段NEW_COL1带有默认值如下语句不带NOT NULL

ALTER TABLE t ADD NEW_COL1 CHAR(10) DEFAULT 'TESTCOLUMN'

SQL> ALTER TABLE test ADD NEW_COL1 CHAR(10) DEFAULT 'TESTCOLUMN';

表已更改。

已用时间:  00: 00: 43.23

SQL>

4. 向表中添加一个新的字段NEW_COL2带有默认值语句中带上 NOT NULL

ALTER TABLE test ADD NEW_COL2 CHAR(10) DEFAULT 'TESTCOLUMN' NOT NULL;

SQL> ALTER TABLE test ADD NEW_COL2 CHAR(10) DEFAULT 'TESTCOLUMN' NOT NULL;

表已更改。

已用时间:  00: 00: 00.25

惊奇的发现两者差别太大了带not null的用时不到1秒

这是11g对新增一个not null字段带有默认值进行了优化，默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中

如下：可以查询test表中添加的默认值

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'TEST' and owner='IMUSE01';

OBJECT_ID

----------

74063

SQL> select COLNUM,BINARYDEFVAL from ecol$ where TABOBJ#= 74063;

COLNUM

----------

BINARYDEFVAL

--------------------------------------------------------------------------------

         4

54455354434F4C554D4E

默认值可以转换一下：

SQL> SET SERVEROUT ON

SQL> DECLARE

  2  V_BLOB VARCHAR2(32767) DEFAULT '54455354434F4C554D4E';

  3  BEGIN

  4  FOR I IN 1..LENGTH(V_BLOB)/2 LOOP

  5  DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I - 1) * 2 + 1, 2), 'XXX')));

  6  END LOOP;

  7  DBMS_OUTPUT.NEW_LINE;

  8  END;

  9  /

TESTCOLUMN

PL/SQL 过程已成功完成。

SQL>