最近大家讨论到各类数据库在新增字段带默认值时是否会锁表的问题,发现Oracle在不同的版本其实是有对应优化的,更新了下之前的知识点。
主要考虑以下三种情形,例如:
- 不加default:alter table t1 add tel varchar2(20);
- default+not null:alter table t1 add tel varchar2(20) default '13888888888' not null;
- 仅加default:alter table t1 add tel varchar2(20) default '13888888888' ;
加列方式 / 版本 | 11g之前 | 11g | 12c |
不加default | 不需全表更新 | 不需全表更新 | 不需全表更新 |
default+not null | 全表更新 | 不需全表更新 可由隐含参数_ADD_COL_OPTIM_ENABLED控制 | 不需全表更新 可由隐含参数_ADD_COL_OPTIM_ENABLED控制 |
仅加default | 全表更新 | 全表更新 | 不需全表更新 更新数据字典sys.col$中default$的值, 在sys.ecol$中记录默认值,且较11g会记录更多信息,在查询要用到时直接读取 |
全表更新对大表意味着更长的锁表时间,更大的事务量,更大的undo占用量,更长的业务影响时间。在11g及之前,这都是一个需要谨慎考量的变更,而从12c开始,影响明显降低。
具体的原理很多文章都有通过10046 trace分析,可以参考:
Oracle 11g中 ADD COLUMN 功能增强 说明_oracle column after-CSDN博客
oracle大表添加字段default经验分享_ITPUB博客
add column时default null的问题 – OracleBlog
oracle column default remove,Oracle 12c column add default value功能增强_令和时代的柯南的博客-CSDN博客
ORACLE-BASE - DEFAULT ON NULL FOR INSERT AND UPDATE in Oracle Database 23c