Oracle 11.2.0.4.0版本下大表添加默认值字段影响因素
最近生产有个需求，要对大表添加一个字段并且带有默认值9，然后再将默认值更改为0。
11g以后，添加带有默认值的字段，如果有NOT NULL限制是非常快的。
12c以后，不用有NOT NULL也可以非常快的完成。
但是我在测试环境测试了下，发现添加速度非常慢，无论是否带有NOT NULL限制。
表：
16:32:35 SYS@zkm(1012)> desc zkm.test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38) 16:42:00 SYS@zkm(1012)> select count(*) from zkm.test; COUNT(*) ---------- 1920001 Elapsed: 00:00:00.04
添加字段name并带有默认值'oracle',非常慢。
alter table zkm.test add name varchar2(20) default 'oracle' not null;
查看mos发现，11g的这个新特性受到隐含参数"_ADD_COL_OPTIM_ENABLED"的影响。
默认该参数值为true，表示启用。
看了下我的环境，这个参数被设置为false。
于是，尝试在会话级别将此参数改为true后，添加就特别快。
17:13:20 SYS@zkm(1012)> alter session set "_ADD_COL_OPTIM_ENABLED"=true; Session altered. Elapsed: 00:00:00.00 17:13:32 SYS@zkm(1012)> alter table zkm.test add name varchar2(20) default 'oracle' not null; Table altered. Elapsed: 00:00:00.86 17:13:49 SYS@zkm(1012)> alter table zkm.test drop column name; Table altered. Elapsed: 00:00:04.51 17:14:18 SYS@zkm(1012)> alter table zkm.test add name varchar2(20) default 'oracle';
卡住......
12c后就不要求需要NOT NULL限制了，不过也是受到参数"_add_nullable_column_with_default_optim"影响。
可以使用以下形式的 SQL 检查哪些列已添加并启用优化：
select owner, object_name, name from dba_objects, col$ where bitand(col$.PROPERTY,1073741824)=1073741824 and object_id=obj#;
经测试，临时更改"_ADD_COL_OPTIM_ENABLED"为true来加快添加NOT NULL默认值字段的速度是不影响后续操作的。
包括后续更改默认值为其他的值，使用数据泵导出表导入表。
参考链接
Init.ora Parameter "_ADD_COL_OPTIM_ENABLED" [Hidden] Reference Note (文档 ID 1492674.1)
Hidden Column "SYS_NC000XX$" Being Added When A New Column Is Added To The Table. (文档 ID 2277937.1)