新增字段的一點一滴技巧

2019 年 12 月 25 日
筆記

在Oracle中給表新增字段的需求，已經寫了不少文章了，太多的經歷告訴我們，一個簡單的需求，如果不了解背後的原理，就很可能出現性能問題。

在11g中，和新增字段相關的結論如下，

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

如上的實驗，可以參考，

《alter table新增字段操作究竟有何影響？(上篇)》

《alter table新增字段操作究竟有何影響？(下篇)》

巧了，這次上線同樣有個需求，向大表增加一個字段，未設置非空約束，但是設置的默認值是null，符合(2)的條件，本以為這是有問題的，但是，實際上，結論不太對，他的執行時間，非常短，所以(2)應該加例外，即如果使用非空默認值，同時，沒設置NOT NULL非空約束的方式新增字段，那麼執行時間會很久，取決於表中數據量的大小。

測試記錄如下，200萬記錄的表，未設NOT NULL，新增字段，不使用默認值和使用默認值是null，執行時間上，是等價的，

SQL> select count(*) from t;    COUNT(*)  ----------     2000000    --設置NOT NULL，使用默認值的方式新增字段  SQL> alter table t add a1 varchar2(1) default 'a' not null;  Table altered.  Elapsed: 00:00:00.07    --未設NOT NULL，不使用默認值的方式新增字段  SQL> alter table t add a2 varchar2(1);  Table altered.  Elapsed: 00:00:00.02    --未設NOT NULL，使用默認值null的方式新增字段  SQL> alter table t add a3 varchar2(1) default null;  Table altered.  Elapsed: 00:00:00.07    --未設NOT NULL，使用默認值的方式新增字段  SQL> alter table t add a4 varchar2(1) default 'c';  Table altered.  Elapsed: 00:01:50.52

對於不同版本，對新增字段的支持在不斷演進，《新增非空約束字段在不同版本中的演進》提到過，11g的官方文檔對新增默認值字段的描述部分，明確指出非空的NOT NULL約束包含默認值的情況下，是將默認值存儲於數據字典中，不用更新表，

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

由於當時我沒有12c的真實環境，所以未做驗證，巧了，看到同事albert最近(http://albertdba.com/?p=1560)發表了篇文章，在Oracle 12c版本中，論證了對不存在非空約束的列添加默認值時，採用同樣的方式，只會更新數據字典，之後新數據才會更新，不會update之前現有列數據，效率非常高，