oracle 11g對大表中新增DEFAULT值的NOT NULL欄位速度有大幅度的提升

http://blog.csdn.net/weiwangsisoftstone/article/details/7674916

今天同事問我一個問題他說在一張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>