新增字段的一点一滴技巧

柔光的暖阳◎ 2023-06-23 15:22 49阅读 0赞

在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]》

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

巧了，这次上线同样有个需求，向大表增加一个字段，未设置非空约束，但是设置的默认值是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

对于不同版本，对新增字段的支持在不断演进，《[新增非空约束字段在不同版本中的演进][Link 1]》提到过，11g的官方文档对新增默认值字段的描述部分，明确指出非空的NOT NULL约束包含默认值的情况下，是将默认值存储于数据字典中，不用更新表，

![format_png][]

12c中描述允许为空的字段，若有默认值，不会更新已存数据，而是会借助数据字典完成存储，这种新特性的适用范围更广了，  
![format_png 1][]

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

![format_png 2][]

![format_png 3][]

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

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

(1) 11g add defualt

![format_png 4][]

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

![format_png 5][]

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

![format_png 6][]

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

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

《[探寻大表删除字段慢的原因][Link 3]》

《[大表删除字段为何慢？][Link 4]》

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

![format_png 7][]

[alter table]: https://blog.csdn.net/bisal/article/details/45418303
[alter table 1]: https://blog.csdn.net/bisal/article/details/49182025
[Link 1]: https://blog.csdn.net/bisal/article/details/53242867
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW5SWlFldW1aZXhpYkk0OVRwMVF0bUVuU2d2aWJ5Q3QydXNzVmlhUnppYkZUaWFNWmhjUWY5eWVNYnRrZW55ODhRdG5HeExBMmZock9lNEVkQS82NDA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW5SWlFldW1aZXhpYkk0OVRwMVF0bUVuSVRMSDV1b0pGSFBpYlZKeURUdnRhYzRBaWNTaWJmU2ljaWMzTGgxVGdIMlVqQmliWFhoMHV1c1dROVZnLzY0MA?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWt6S21WNVUyOWM0dnUwS1BMZ29mNE5uS013Wm0yRXdoamMwQmdmTmtJQVZ6b3BScVIxSXNrU0psUjVUMng2cGFKcUpiczhLSzJpYW1nLzY0MA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWt6S21WNVUyOWM0dnUwS1BMZ29mNE5rZ2pLTmVYdEp4c3dEdTlmWEpOQ0hZTVJaOU1OMHJKZ2Z6b0hSa3V5Y3lTOXp4a3hPZ1RhNlEvNjQw?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWt6S21WNVUyOWM0dnUwS1BMZ29mNE5XdEQ2cjluTDlVNDRYQ0ZyVER3ZVJ4T2VoUVd3Y1RrbWFnaGlhU3kxMnhSMk80YklvdVhneVRRLzY0MA?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWt6S21WNVUyOWM0dnUwS1BMZ29mNE5JS082cmliVzlYMm11SU9YbncwU280ZDMweVNobFNYWk9UQ3lFdndFN0hkeWs4TklMZGtwd2t3LzY0MA?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWt6S21WNVUyOWM0dnUwS1BMZ29mNE5pYzdmQkpyUTFyNGY4V0FqYTA5MjlmME5FNTVhSm5XTUVReFNvaWFlaWM4ZWliME1Cb3VDVXdVQlRBLzY0MA?x-oss-process=image/format,png
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814beaddf6c2fcd242591e4892a3ac21a272f3307b9771a8ca9b76ef96b1a7bd8249c564fa&idx=1&mid=2247487001&scene=21&sn=15394f15aedee7f8f261fa7294df08a5#wechat_redirect
[Link 3]: https://blog.csdn.net/bisal/article/details/86635285
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8154fdddf6ddeb66c11764b10710c6f86434f96219f82d6637eb88597dd3f0b89333db8eda&idx=1&mid=2247487758&scene=21&sn=4e6696b62b6d19c4faf36a04266521e9#wechat_redirect
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWx3emVNT1hzdHpiR2ljRjRuOEl2alNSaWNkM1JCOUxWN0N4cW1IaDJHeXFpY2JTMW5aUkhtaWNmRmV3R2VmVTVVV010ejRYZGNJbzdHaWNwZy82NDA?x-oss-process=image/format,png