oracle大表添加字段default经验分享

最新推荐文章于 2025-10-29 11:28:53 发布

转载最新推荐文章于 2025-10-29 11:28:53 发布 · 4.8k 阅读

oracle 专栏收录该内容

20 篇文章

订阅专栏

当Oracle单表数据量巨大时，使用ALTER TABLE添加带有默认值的字段会导致效率低下且安全性差。本文通过实例展示了如何将操作拆分为两步，先添加字段再设置默认值，以提高效率并降低风险。

当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) default '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) default 'Y';更新存量数据可放到业务较少的凌晨跑。