oracle大表添加字段default经验分享
当oracle单表数据量上亿时，对表进行alter table aa add column_1 varchar2(2) defalut 'Y';时，效率及安全性是必须考虑的因素。
本帖以2亿的数据表aa举例：
alter table aa add column_1 varchar2(2) defalut 'Y';
Table altered
Executed in 3600 seconds
直接用了一个小时，而且在这过程中是加上了表级锁，也就是连查询都是会被等待的，这是相当危险的操作。
为什么会这样，原来oracle在执行上面语句时不仅要更新数据字典，还会刷新全部的记录。一次update 2 亿条数据当然需要一个小时，并且还会导致undo空间暴涨。
如果我们把更新数据字典和更新字段值分开，会不会好一点？
alter table aa add column_1 varchar2(2);
Table altered Executed in 0.016 seconds
alter table aa modify column_1 varchar2(2) defalut 'Y';
Table altered
Executed in 0.003 seconds
答案是快100万倍，那效果呢？经测试发现在第二种方法不会更新以前的数据，我们可以自己写一个匿名块循环update，一次commit 10000条，达到效率与安全的完美结合。
结论：alter table add 语句加上defalut时会刷新存量数据并产生表级锁，需慎用。特别是大表，生产环境，业务产生期间就应该禁止此操作。
改为add table add不带缺省值，接着来个alter table aa modify column_1 varchar2(2) defalut 'Y';更新存量数据可放到业务较少的凌晨跑。
本帖以2亿的数据表aa举例：
alter table aa add column_1 varchar2(2) defalut 'Y';
Table altered
Executed in 3600 seconds
直接用了一个小时，而且在这过程中是加上了表级锁，也就是连查询都是会被等待的，这是相当危险的操作。
为什么会这样，原来oracle在执行上面语句时不仅要更新数据字典，还会刷新全部的记录。一次update 2 亿条数据当然需要一个小时，并且还会导致undo空间暴涨。
如果我们把更新数据字典和更新字段值分开，会不会好一点？
alter table aa add column_1 varchar2(2);
Table altered Executed in 0.016 seconds
alter table aa modify column_1 varchar2(2) defalut 'Y';
Table altered
Executed in 0.003 seconds
答案是快100万倍，那效果呢？经测试发现在第二种方法不会更新以前的数据，我们可以自己写一个匿名块循环update，一次commit 10000条，达到效率与安全的完美结合。
结论：alter table add 语句加上defalut时会刷新存量数据并产生表级锁，需慎用。特别是大表，生产环境，业务产生期间就应该禁止此操作。
改为add table add不带缺省值，接着来个alter table aa modify column_1 varchar2(2) defalut 'Y';更新存量数据可放到业务较少的凌晨跑。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30557618/viewspace-1834042/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
