编辑手记：本文来自上周四云和恩墨大讲堂课程的整理。作者通过锁、索引、约束等的情况验证了表的新增字段对不同版本的Oracle数据库产生的影响。
作者简介
很多人在做一些表设计时会留出几个reverse的字段，这样需要的时候直接用就行了，不需要新增字段的操作，但此时设计的字段类型、长度等都是预计的，未来是否可用并不好说。那么为什么要这样做？
新增字段的操作究竟有什么影响？增加表字段的时候，是否会锁表？对DML、DDL有什么影响？搞清楚这些，才能对上面的问题给出科学的答案。
为了证明增加字段的操作究竟做了什么，有什么影响，打算使用10046事件来看看，一个11g的库，创建测试表T，执行10046事件。
使用tkprof格式化trace文件，关键的信息如下：
省略几百行。。。
就是一个alter table增加字段的操作，trace文件如此之长，还是很崩溃的。。。
但通过一些关键的点，应该可以看出端倪：
1. 10046开始记录后的第一条语句：
说明此时对T以NOWAIT方式，加了ROW EXCLUSIVE模式锁。
2. 接下来就是执行的新增字段的SQL语句：
3. 然后就是各种查，有数据字典表的，有PLSQL，感觉就一个字：乱，再加一字：晕。但大部分SQL执行的时间都在x毫秒。
4. 我们直接看关闭10046事件之前的最后一句：
执行的是col$表的更新语句。从表名看，col$是列的数据字典表，使用了绑定变量，那这些值是什么，就成了问题的关键。
5. 使用v$sql_bind_capture可以查看仍在内存中的SQL绑定变量值，找了其中一些，有的已经查不到了，但上面10046前的最后一句SQL使用的绑定变量值如下：
看来可以解释许多问题了，
(1) update col$语句中一共有20个绑定变量，上面SQL显示的绑定变量值，同样是20个，说明是对应的。
(2) update col$的where条件是编号为1和2的绑定变量值，这里显示的是74592和SEX。
(3) 再来看看这个74592是什么，从上面SQL的条件obj#=:1，猜测是一个对象，
74592就是这张表T，SEX是新增字段名。于是，可以猜测，之前已经将SEX字段加入了相应的数据字典表，最后一句就是更新col$中T表SEX字段的一些信息。
6. trace文件的最后列出了所有递归调用语句的消耗统计：
可以看出，为了一个alter table新增字段的操作，总共执行了几百次的内部SQL，大部分是通过索引方式扫描，执行的时间是100多毫秒，很快，因此感觉不到，但实际Oracle自己做了这么多后台操作，感叹他的强大，一个简单的新增字段操作，就有如此复杂的实现，但性能上基本让你感觉不到，佩服得五体投地。
上面我们了解到了新增字段的SQL语句背后，Oracle大致做了什么操作。接着，我们通过实验来看下不同方式新增字段的效率。
实验1：
SQL> settiming on
SQL> altertable t add add_a number;
Table altered.
Elapsed:00:00:00.29
新增一个允许NULL，且无默认值的字段，用时0.29秒。从10046的trace文件看他获得的是一个ROW EXCLUSIVE模式锁：
LOCK TABLE"T" IN ROW EXCLUSIVE MODE NOWAIT
实验2：
SQL> altertable t add add_b number default 0;
Table altered.
Elapsed:00:00:59.34
新增一个允许NULL，但有默认值的字段，用时59秒。从10046的trace文件看，他会首先用EXCLUSIVE模式锁来锁定表。
LOCK TABLE"T" IN EXCLUSIVE MODE NOWAIT
同时，在最后执行了更新字段ADD_B为默认值的操作：
update"T" set "ADD_B"=0;
因此不难想像，前台反映的现象就是这个操作处于hang状态，并且影响其他session对该表的操作，为什么耗时这样久，原因就是这个操作需要更新表中所有记录该字段为默认值，另外，还会因为数据量的增加，可能需要更多的UNDO空间，进而可能因为一条新增字段的操作，导致整个库的UNDO表空间不够用，不仅影响对这张表的正常增删改操作(因为获取了最高级别EXCLUSIVE锁)，还有可能影响其他业务功能(因为UNDO表空间不够用)。
实验3：
SQL> altertable t add add_c number default 0 not null;
Table altered.
Elapsed:00:00:00.16
新增一个包含NOT NULL约束，有默认值的字段，用时0.16秒。
从10046的trace文件看，会获得一个ROW EXCLUSIVE模式锁来锁定表。
“LOCKTABLE “T” IN ROW EXCLUSIVE MODENOWAIT“`
锁的级别比实验2要低，而且该默认值是存储于数据字典表中的，并不是保存在原表记录上，即新增一个NOTNULL和默认值的字段，以后每次需要使用该字段时，默认值都是从数据字典中查询到的，这样就减少了新增字段时的DDL语句时间，也减少了存储空间(不用每条需要使用默认值的记录都存储默认值)。
像上面第一次增加列的操作时，会同时更新sys.ecol$和sys.col$数据字典表，若以后再修改这个默认值，则只是会修改sys.col$的值，且以后每次查询也是从sys.col$的default$列获取默认值，我们可以根据sys.eclo$、sys.col$和dba_objects查询相关表和字段信息，
尝试修改默认值，从0变为1，
再次查看sys.ecol$，未变化，
查看sys.col$，发现default$已经变为了1，
11g的官方文档也介绍了，Oracle增加了这种新特性，对新增字段操作做了上面这些优化，
如果新增一个含有默认值的字段，那么会立即更新每一行，在更新过程中，会有一个EXCLUSIVE级别的锁在该表上。如果指定NOT NULL和默认值，则会进行优化，降低阻止DML操作的时间。如果增加一个仅有NOT NULL的约束字段，那么需要表不能包含任何记录，否则就需要必须指定一个默认值，这也好理解，如果执行之前有记录，又要求NOT NULL，那么之前的记录字段默认值是什么就需要指定才行。
实验4：
SQL> selectcount(*) from t;
1000000
SQL> altertable t add add_h number not null;
alter table tadd add_h number not null
*
ERROR at line1:
ORA-01758:table must be empty to add mandatory (NOT NULL) column
新增一个仅有NOT NULL约束，没有默认值的字段，则需要表为空。顺带提一句，删除表字段的操作：
SQL> altertable t drop column add_b;
Table altered.
Elapsed:00:00:43.44
从10046的trace文件看，也是获得了一个EXCLUSIVE锁，进而更新的过程中是对整张表的DML操作有影响的。
总结一下：
1. 11g以上的版本，如果使用NOT NULL和默认值的方式新增字段，那么执行时间会大大降低。且只会有一个ROW EXCLUSIVE级别锁。
2. 11g以上的版本，如果使用默认值，没有NOT NULL约束的方式新增字段，那么执行时间会很久，取决于表中数据量的大小，获得的是EXCLUSIVE级别锁，期间会影响所有记录的DML操作，可能会因UNDO不足对其他操作有影响。
3. 11g以上的版本，如果新增字段没有默认值，也没有NOT NULL约束，则还是会使用ROW EXCLUSIVE模式锁，但由于不需要更新字段值，执行时间也是比较短。
这样一来，如何选择11g上新增字段的方式，看来是有一个比较清晰的方向了。
最后，我们说一个和新增NOT NULL字段有关的小话题，可能有很多同学之前看过杨长老前段时间连续发表过的两篇关于NOT NULL字段的文章，可以参考如下：
1. 非空字段空值对查询的影响，http://yangtingkun.net/?p=1481
2. 非空字段空值的产生，http://yangtingkun.net/?p=1483
简单总结一下，11.2.0.3的库，
1.使用where type is null和is notnull得到的记录结果判断值为非空。
2.使用dump(type)和nvl(type, ‘is null’)得到的记录结果判断值为空。
表定义中此字段为DEFAULT ‘’ NOT NULL，事实证明(2)是正确的，之所以有(1)的结论，原因是CBO太智能了。
1、对于IS NOT NULL，type字段定义为NOT NULL，此SQL明显违反了表中的约束条件，则会在执行计划最上层增加一个NULL IS NOT NULL恒为假的条件，根本不需要真正执行这个SQL，直接返回0条记录。
2、对于IS NULL，由于查询条件满足约束的条件，因此Oracle会做全表扫描，并且省略了type is not null的过滤，直接返回所有记录，就造成了type非空的假象。
出现以上问题的核心，就是为何有为空的记录存储于有NOT NULL非空约束的表中。原因就是前面介绍过的11g新特性，新增一个有默认值的NOT NULL约束的字段，默认值不会像以前一样，插入每条记录中，而是会存储于数据字典表，Oracle允许NOT NULL列默认值为NULL，因此对于11g来说，需要禁止DEFAULT为NULL的这种行为。
这种新增非空约束字段在不同版本中确实有一些细节的变化，下面做一些简单测试。
首先，创建测试表，插入一条数据，新增列为NOT NULL且默认值是”的字段：
create tablebisal (id number);
insert intobisal values(1);
alter tablebisal add name varchar2(10) default '' not null;
10.2.0.3库，从报错信息看ORA-01407，不能更新NAME列为空，可以看出此时是要将表中已存在记录的新列name做UPDATE设置为默认值的操作，由于有非空约束，因此不允许。
11.2.0.1库，可以新增字段，表中已存记录该值确实为空，即允许一个有NOT NULL约束的字段包含NULL值。
12.1.0.2库，我们可以看出和10g一样，禁止新增一个默认值为NULL的NOT NULL约束字段，但报错信息变了，ORA-01758: table mustbe empty to add mandatory (NOT NULL) column，这个错误号在之前的版本有定义，不是新号。
根据错误提示，我们删除表中数据，再新增字段，可以增加，但不能再插入一条NULL至这个非空约束字段。
我们再看下官方文档的描述，11g中对于新增默认值字段的描述部分，明确指出NOT NULL约束包含默认值的情况下，是将默认值存储于数据字典中。
12c中描述允许为空的字段，若有默认值，不会更新已存数据，而是会借助数据字典完成存储，这种新特性的适用范围更广了。
至此，12c修复了11g中这个非空约束字段允许保存空值的bug，同时又支持11g新增默认值非空字段使用数据字典存储的特性，并且做了扩展支持，满足范围更大了。可以说，小问题隐藏了大智慧。
如何加入"云和恩墨大讲堂"微信群
搜索 盖国强（Eygle） ：eyygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。
Oracle 12c Global Data Services
