新增字段的一点一滴技巧

新增字段的一点一滴技巧

在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的真实环境，所以未做验证，巧了，看到同事albert最近(http://albertdba.com/?p=1560)发表了篇文章，在Oracle 12c版本中，论证了对不存在非空约束的列添加默认值时，采用同样的方式，只会更新数据字典，之后新数据才会更新，不会update之前现有列数据，效率非常高，

12c支持了原先11g不支持的新增默认值非空字段使用数据字典存储的特性。

albert还从执行计划角度补充了，如果不更新列，读取数据时会有nvl或者decode函数的开销，

(1) 11g add defualt

(2) 11g add default not null，会有NVL函数进行转换，

(3) 12c add defualt，同样会有DECODE函数进行转换，是否从数据字典中获取还是从具体含有值的block中读取，

利用这个特性，在特殊场景下，是可以作为一种优化的手段，例如《千万级表数据更新的需求》这个案例中，通过这个特性，更新一张千万级表的数据，从原来方案需要几个小时，缩短到了1分钟，神不神奇？

除了新增字段，删除字段同样有讲究的，可以参考，

《探寻大表删除字段慢的原因》

《大表删除字段为何慢？》

其实，无论是什么技术，什么知识，了解透彻，总会有好处的。