問題：某電信系統業務，其中有一張表有上億條資訊記錄，而且很重要；要求在不停庫和不鎖表的情況下；對該表增加一個新的欄位，如何實現。
在以前，在表中新增一個NOT NULL欄位是件十分痛苦的事情，尤其是在表很大的情況，不但執行速度慢（會產生DDL鎖，影響其它使用者操作），而且由於資料長度的增加，很容易造成表中大量的行連結情況，影響效能。
在11g中，這種情況得到了徹底的改善，Oracle透過在資料字典（ecol$）中記錄DEFAULT值，避免了繁重的更新操作，增加非空欄位的時間和增加一個可空欄位的時間完全一樣。
下面先看中的情況：
SQL> select * from v$version where rownum<2;

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

SQL> create 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

Oracle11g新特性 - 快速線上新增not null欄位

相關文章