新增字段的一点一滴技巧-CSDN博客

本文链接：https://blog.csdn.net/bisal/article/details/103626212

在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之前现有列数据，效率非常高，