ORACLE TABLE有大量记录,如果增加字段带default值需要很长时间

最新推荐文章于 2023-08-25 13:46:25 发布

mikean

最新推荐文章于 2023-08-25 13:46:25 发布

阅读量9.1k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.csdn.net/mikean/article/details/24872955

Oracle 专栏收录该内容

54 篇文章

订阅专栏

本文探讨了在Oracle数据库中向大型表添加字段的不同方法及其性能影响。对比了直接添加带有默认值的字段与分两步执行（先添加字段再设置默认值）的方法，并介绍了在不同Oracle版本中的表现差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

manpower.s_salary 375w記錄，需增加栏位add_sh number(7,2) default 0

1.先排除无人使用

SELECT P.SPID,s.STATUS,s.terminal,s.machine,s.program,s.osuser,s.CLIENT_INFO,s.LOGON_TIME

FROM V$SESSION S,V$ACCESS A,V$PROCESS P
WHERE S.SID=A.SID
AND S.PADDR=P.ADDR

AND A.OBJECT='S_SALARY'

2.alter table manpower.s_salary add (add_sh number(7,2) default 0);
需要时间21分钟。（中午下班时间执行）

分2步执行，效果显著提高

alter table manpower.s_salary add (add_sh number(7,2));
需要时间0.25秒。

alter table manpower.s_salary modify (add_sh number(7,2) default 0);

需要时间0.25秒。

oracle版本为10.2.05

还是有的：
1）在ALTER sql中有带缺省值，ORACLE会直接刷新全部的记录。
2）在ALTER sql中没有带缺省值，ORACLE只会影响到后来的记录。（明白快是有隐秘的秘诀）

在选择哪种方法时，要更加具体的情况来实施：是否有INDEX，是否在应用的高并发阶段，等等

------------------------------------------以下为转载http://yinzhihua2008.blog.163.com/blog/static/7943067201211241056654/----------------------------------

ORACLE数据表有很多数据如果增加字段需要很长时间

关于创建的方式哪种更快，需要综合考虑，由于楼主的库是非归档模式的，所以估计是个测试库。
对于测试环境，楼主的两种加载数据的方式都可行，我测试环境没有那么大的表，只能拿一个有1500万行，1G大小的表测试了一下，采用数据泵方式用了5分钟（导出加导入），采用insert /*+append*/ 方式用时216秒(如果加载新增字段为not null，需要260秒)，看似insert /*+append*/的效率更高一些；不过有几点要注意的：
1.在正式的生产环境，考虑到数据可恢复的问题，可能insert /*+append*/是不能被接受的
2.这两种方式目标表的默认值只对新插入的数据有效，而对导入自原表中的数据无效。
3.采用数据泵模式，新加的字段不能为not null
对于可以接受停机的生产库，为了保证数据的可恢复，建议采用数据泵或者直接路径加载的sql loader试试
但是，现在越来越多的oracle充当企业关键数据库，可能不会给予足够的停机时间，此时可以采用在线重定义等高可用方式。
或者，按照以下步骤：
1.在表上增加一个新字段，不带默认值，这个操作应该很快
alter table test add test number;
2.把此字段改为默认值，这样，以后插入的数据就包含默认值了，但是修改之前的字段值仍然为空
alter table test modify test default 88888888;
3.如果需要将修改前的默认值加入相关的历史数据，可以做一个批量更新的存储过程，定义比如每1000条一次commit，以减小生产库的压力。
4.更新完成后，可以将字段设置为not null模式
总之，在生产库的真实环境中（尤其是24X7库），有时候速度并不是唯一要考虑的因素，还要考虑数据的可恢复性，操作时对数据库的性能影响，以及停机时间，这就是为什么oracle会提供速度并不突出的在线重定义的原因。有时为了减轻对生产库的压力，我也会故意在循环插入、更新和删除的存储过程中加入延迟，以防治日志过于频繁的切换。
上述测试是基于10g的，在11g中，看来oracle已经意识到了这个问题，所以可以直接采用添加默认值的语法，这个更新应该时间很短，因为你查到以前数据上新加的字段上已经有默认值了，但是这是通过oracle内部函数计算后显示出来的，而不是真实存在于数据库中的，只有以后加入的数据，默认值才会真实插入数据库表中，所以11g中你可以使用下面语句：
alter table test add test number default 88888888 not null;