oracle大表添加字段default经验分享
Oracle 分享 添加 经验 字段 Default 大表
2023-09-11 14:20:17 时间
当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';更新存量数据可放到业务较少的凌晨跑。
http://blog.itpub.net/29827022/viewspace-1593139/
相关文章
- Oracle报错注入总结
- 【oracle】统计信息的恢复和备份
- 《oracle每天一练》Merge Into 语句代替Insert/Update在Oracle中的应用实战
- Oracle RAMN 备份解决方案一例
- 循环插入oracle 存储过程
- ORACLE执行计划
- Oracle RAC 11g r2查询太慢
- 【Oracle 集群】ORACLE DATABASE 11G RAC 知识图文详细教程之缓存融合技术和主要后台进程(四)
- 【Oracle 集群】ORACLE DATABASE 11G RAC 知识图文详细教程之RAC 工作原理和相关组件(三)
- 利用dblink实现oracle对oracle数据库的同步
- Oracle 杀死INACTIVE,SNIPED会话
- Oracle V$SESSION_EVENT 和 V$SYSTEM_EVENT
- Oracle 11g 在使用时应该启动哪些服务?
- Oracle创建用户并赋予权限
- Oracle fga审计有这几个特性
- 【转】Oracle Outline使用方法及注意事项
- Oracle 12c中SQLPlus操作使用(包含实验二ORACLE SQL*PLUS环境与查询的详细操作解释)