編輯手記：本文來自上周四云和恩墨大講堂課程的整理。作者通過鎖、索引、約束等的情況驗證了表的新增字段對不同版本的Oracle數據庫產生的影響。

作者簡介

劉晨，網名bisal，Oracle 10g/11g OCM，并國內首批Oracle YEP成員，博客：blog.itpub.net/bisal

很多人在做一些表設計時會留出幾個reverse的字段，這樣需要的時候直接用就行了，不需要新增字段的操作，但此時設計的字段類型、長度等都是預計的，未來是否可用并不好說。那么為什么要這樣做？

新增字段的操作究竟有什么影響？增加表字段的時候，是否會鎖表？對DML、DDL有什么影響？搞清楚這些，才能對上面的問題給出科學的答案。

為了證明增加字段的操作究竟做了什么，有什么影響，打算使用10046事件來看看，一個11g的庫，創(chuàng)建測試表T，執(zhí)行10046事件。

使用tkprof格式化trace文件，關鍵的信息如下：

省略幾百行。。。

就是一個alter table增加字段的操作，trace文件如此之長，還是很崩潰的。。。

但通過一些關鍵的點，應該可以看出端倪：

1. 10046開始記錄后的第一條語句：

說明此時對T以NOWAIT方式，加了ROW EXCLUSIVE模式鎖。

2. 接下來就是執(zhí)行的新增字段的SQL語句：

3. 然后就是各種查，有數據字典表的，有PLSQL，感覺就一個字：亂，再加一字：暈。但大部分SQL執(zhí)行的時間都在x毫秒。

4. 我們直接看關閉10046事件之前的最后一句：

執(zhí)行的是col$表的更新語句。從表名看，col$是列的數據字典表，使用了綁定變量，那這些值是什么，就成了問題的關鍵。

5. 使用v$sql_bind_capture可以查看仍在內存中的SQL綁定變量值，找了其中一些，有的已經查不到了，但上面10046前的最后一句SQL使用的綁定變量值如下：

看來可以解釋許多問題了，

(1) update col$語句中一共有20個綁定變量，上面SQL顯示的綁定變量值，同樣是20個，說明是對應的。

(2) update col$的where條件是編號為1和2的綁定變量值，這里顯示的是74592和SEX。

(3) 再來看看這個74592是什么，從上面SQL的條件obj#=:1，猜測是一個對象，

74592就是這張表T，SEX是新增字段名。于是，可以猜測，之前已經將SEX字段加入了相應的數據字典表，最后一句就是更新col$中T表SEX字段的一些信息。

6. trace文件的最后列出了所有遞歸調用語句的消耗統(tǒng)計：

可以看出，為了一個alter table新增字段的操作，總共執(zhí)行了幾百次的內部SQL，大部分是通過索引方式掃描，執(zhí)行的時間是100多毫秒，很快，因此感覺不到，但實際Oracle自己做了這么多后臺操作，感嘆他的強大，一個簡單的新增字段操作，就有如此復雜的實現，但性能上基本讓你感覺不到，佩服得五體投地。

上面我們了解到了新增字段的SQL語句背后，Oracle大致做了什么操作。接著，我們通過實驗來看下不同方式新增字段的效率。

實驗1：

SQL> settiming on

SQL> altertable t add add_a number;

Table altered.

Elapsed:00:00:00.29

新增一個允許NULL，且無默認值的字段，用時0.29秒。從10046的trace文件看他獲得的是一個ROW EXCLUSIVE模式鎖：

LOCK TABLE"T" IN ROW EXCLUSIVE MODE NOWAIT

實驗2：

SQL> altertable t add add_b number default 0;

Table altered.

Elapsed:00:00:59.34

新增一個允許NULL，但有默認值的字段，用時59秒。從10046的trace文件看，他會首先用EXCLUSIVE模式鎖來鎖定表。

LOCK TABLE"T" IN EXCLUSIVE MODE NOWAIT

同時，在最后執(zhí)行了更新字段ADD_B為默認值的操作：

update"T" set "ADD_B"=0;

因此不難想像，前臺反映的現象就是這個操作處于hang狀態(tài)，并且影響其他session對該表的操作，為什么耗時這樣久，原因就是這個操作需要更新表中所有記錄該字段為默認值，另外，還會因為數據量的增加，可能需要更多的UNDO空間，進而可能因為一條新增字段的操作，導致整個庫的UNDO表空間不夠用，不僅影響對這張表的正常增刪改操作(因為獲取了最高級別EXCLUSIVE鎖)，還有可能影響其他業(yè)務功能(因為UNDO表空間不夠用)。

實驗3：

SQL> altertable t add add_c number default 0 not null;

Table altered.

Elapsed:00:00:00.16

新增一個包含NOT NULL約束，有默認值的字段，用時0.16秒。

從10046的trace文件看，會獲得一個ROW EXCLUSIVE模式鎖來鎖定表。

“LOCKTABLE “T” IN ROW EXCLUSIVE MODENOWAIT“`

鎖的級別比實驗2要低，而且該默認值是存儲于數據字典表中的，并不是保存在原表記錄上，即新增一個NOTNULL和默認值的字段，以后每次需要使用該字段時，默認值都是從數據字典中查詢到的，這樣就減少了新增字段時的DDL語句時間，也減少了存儲空間(不用每條需要使用默認值的記錄都存儲默認值)。

像上面第一次增加列的操作時，會同時更新sys.ecol$和sys.col$數據字典表，若以后再修改這個默認值，則只是會修改sys.col$的值，且以后每次查詢也是從sys.col$的default$列獲取默認值，我們可以根據sys.eclo$、sys.col$和dba_objects查詢相關表和字段信息，

嘗試修改默認值，從0變?yōu)?，

再次查看sys.ecol$，未變化，

查看sys.col$，發(fā)現default$已經變?yōu)榱?，

11g的官方文檔也介紹了，Oracle增加了這種新特性，對新增字段操作做了上面這些優(yōu)化，

如果新增一個含有默認值的字段，那么會立即更新每一行，在更新過程中，會有一個EXCLUSIVE級別的鎖在該表上。如果指定NOT NULL和默認值，則會進行優(yōu)化，降低阻止DML操作的時間。如果增加一個僅有NOT NULL的約束字段，那么需要表不能包含任何記錄，否則就需要必須指定一個默認值，這也好理解，如果執(zhí)行之前有記錄，又要求NOT NULL，那么之前的記錄字段默認值是什么就需要指定才行。

實驗4：

SQL> selectcount(*) from t;

   1000000

SQL> altertable t add add_h number not null;         

alter table tadd add_h number not null

            *

ERROR at line1:

ORA-01758:table must be empty to add mandatory (NOT NULL) column

新增一個僅有NOT NULL約束，沒有默認值的字段，則需要表為空。順帶提一句，刪除表字段的操作：

SQL> altertable t drop column add_b;

Table altered.

Elapsed:00:00:43.44

從10046的trace文件看，也是獲得了一個EXCLUSIVE鎖，進而更新的過程中是對整張表的DML操作有影響的。

總結一下：
1. 11g以上的版本，如果使用NOT NULL和默認值的方式新增字段，那么執(zhí)行時間會大大降低。且只會有一個ROW EXCLUSIVE級別鎖。
2. 11g以上的版本，如果使用默認值，沒有NOT NULL約束的方式新增字段，那么執(zhí)行時間會很久，取決于表中數據量的大小，獲得的是EXCLUSIVE級別鎖，期間會影響所有記錄的DML操作，可能會因UNDO不足對其他操作有影響。
3. 11g以上的版本，如果新增字段沒有默認值，也沒有NOT NULL約束，則還是會使用ROW EXCLUSIVE模式鎖，但由于不需要更新字段值，執(zhí)行時間也是比較短。

這樣一來，如何選擇11g上新增字段的方式，看來是有一個比較清晰的方向了。

最后，我們說一個和新增NOT NULL字段有關的小話題，可能有很多同學之前看過楊長老前段時間連續(xù)發(fā)表過的兩篇關于NOT NULL字段的文章，可以參考如下：

1. 非空字段空值對查詢的影響，http:///?p=1481

2. 非空字段空值的產生，http:///?p=1483

簡單總結一下，11.2.0.3的庫， 

 1.使用where type is null和is notnull得到的記錄結果判斷值為非空。
2.使用dump(type)和nvl(type, ‘is null’)得到的記錄結果判斷值為空。 

表定義中此字段為DEFAULT ‘’ NOT NULL，事實證明(2)是正確的，之所以有(1)的結論，原因是CBO太智能了。
1、對于IS NOT NULL，type字段定義為NOT NULL，此SQL明顯違反了表中的約束條件，則會在執(zhí)行計劃最上層增加一個NULL IS NOT NULL恒為假的條件，根本不需要真正執(zhí)行這個SQL，直接返回0條記錄。
2、對于IS NULL，由于查詢條件滿足約束的條件，因此Oracle會做全表掃描，并且省略了type is not null的過濾，直接返回所有記錄，就造成了type非空的假象。

出現以上問題的核心，就是為何有為空的記錄存儲于有NOT NULL非空約束的表中。原因就是前面介紹過的11g新特性，新增一個有默認值的NOT NULL約束的字段，默認值不會像以前一樣，插入每條記錄中，而是會存儲于數據字典表，Oracle允許NOT NULL列默認值為NULL，因此對于11g來說，需要禁止DEFAULT為NULL的這種行為。

這種新增非空約束字段在不同版本中確實有一些細節(jié)的變化，下面做一些簡單測試。 

首先，創(chuàng)建測試表，插入一條數據，新增列為NOT NULL且默認值是”的字段：

create tablebisal (id number);

insert intobisal values(1);

alter tablebisal add name varchar2(10) default '' not null;

10.2.0.3庫，從報錯信息看ORA-01407，不能更新NAME列為空，可以看出此時是要將表中已存在記錄的新列name做UPDATE設置為默認值的操作，由于有非空約束，因此不允許。

11.2.0.1庫，可以新增字段，表中已存記錄該值確實為空，即允許一個有NOT NULL約束的字段包含NULL值。

12.1.0.2庫，我們可以看出和10g一樣，禁止新增一個默認值為NULL的NOT NULL約束字段，但報錯信息變了，ORA-01758: table mustbe empty to add mandatory (NOT NULL) column，這個錯誤號在之前的版本有定義，不是新號。

根據錯誤提示，我們刪除表中數據，再新增字段，可以增加，但不能再插入一條NULL至這個非空約束字段。

我們再看下官方文檔的描述，11g中對于新增默認值字段的描述部分，明確指出NOT NULL約束包含默認值的情況下，是將默認值存儲于數據字典中。

12c中描述允許為空的字段，若有默認值，不會更新已存數據，而是會借助數據字典完成存儲，這種新特性的適用范圍更廣了。

至此，12c修復了11g中這個非空約束字段允許保存空值的bug，同時又支持11g新增默認值非空字段使用數據字典存儲的特性，并且做了擴展支持，滿足范圍更大了。可以說，小問題隱藏了大智慧。