add column时default null的问题

昨晚在对一个省进行打patch时，发现数据库升级脚本执行了很久还没执行完，查看了一下这个执行了很久的语句：

alter table TABLE_BIG add column_a date default null;

类似这样的语句有好几个，而且由于其中涉及到了流水的历史表——一个非常非常大的分区表，执行时间非常的长！其中的一个已经执行了1个半小时还没执行完毕，当时已经凌晨3点半多，按照这样的速度下去，肯定在早上6点前完成不了升级。

检查脚本后决定在数据库层面如下操作：
（1）中断已经在执行的脚本，把后续执行的：alter table TABLE_BIG add column_a date default null;语句中default null去掉，改成：

alter table TABLE_BIG add column_a date ;

这样就只会修改数据字典，不会对大表的每行记录进行null的check。
（2）如果还是会执行很长的时候，在5点前完成不了数据库脚本的升级的话，考虑回滚，中断脚本的执行，且将已经增加字段的表的列set unused（只修改数据字典，很快；若用drop column的方式则很慢），再启动业务。

修改完成后，在不到一分钟的时间内执行完毕了后续的脚本语句！

呵呵，看来加了default null字段确实会在数据库中做不仅仅是更新数据字典的事情，导致执行速度大大变慢。为了弄清楚到底做了些什么操作，我做了个小实验，打了10046的trace出来（level 12的）。（trace全文见文章末尾附件）

不加default null的trace：

……
=====================
PARSING IN CURSOR #1 len=30 dep=0 uid=262 oct=15 lid=262 tim=12456708650157 hv=4259279858 ad='cac40b18'
alter table xxx add c_xxx date <=========操作的语句！！！
……
=====================
PARSING IN CURSOR #11 len=408 dep=1 uid=0 oct=2 lid=0 tim=12456708675572 hv=3687727603 ad='caa44558'
insert intocol$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:7,0,null,:7),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)<========insert col$增加c_xxx字段！！！
END OF STMT
PARSE #11:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=12456708675564
BINDS #11:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0
   bfp=800003fa00071770 bln=22 avl=04 flg=05
   value=800456
 bind 1: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=18 oacfl2=4000000000000001 size=32 offset=0
   bfp=c0000000c5a1694a bln=32 avl=05 flg=09
   value="C_XXX"     <===========此处为insert的值！！！！
 bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0
   bfp=800003fa00071740 bln=24 avl=02 flg=05
   value=6
 bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0
   bfp=800003fa00071710 bln=24 avl=02 flg=05
   value=6
 bind 4: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0
   bfp=800003fa000716e0 bln=24 avl=02 flg=05
   value=12
 bind 5: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacfl
……

加default null的trace：

……
PARSING IN CURSOR #1 len=43 dep=0 uid=262 oct=15 lid=262 tim=12455097007005 hv=944784935 ad='ca96fa30'
alter table xxx add c_xxx date default null  <=========操作的语句！！！
END OF STMT
……
WAIT #10: nam='global cache cr request' ela= 1135 p1=500 p2=47271 p3=-4611686016431196992
FETCH #10:c=0,e=1300,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=3,tim=12455100885697
=====================
PARSING IN CURSOR #9 len=29 dep=1 uid=262 oct=6 lid=262 tim=12455100885938 hv=3942118436 ad='cd491a10'
update "XXX" set "C_XXX"=null   <=========问题就在这里了！！！会在insert col$前update该表，把值update成null！！
END OF STMT
PARSE #9:c=0,e=4059,p=0,cr=5,cu=0,mis=1,r=0,dep=1,og=4,tim=12455100885929
BINDS #9:
WAIT #9: nam='db file sequential read' ela= 73092 p1=480 p2=57738 p3=1
WAIT #9: nam='db file sequential read' ela= 86228 p1=480 p2=57739 p3=1
WAIT #9: nam='db file sequential read' ela= 221129 p1=480 p2=57740 p3=1
……
WAIT #9: nam='db file sequential read' ela= 374 p1=477 p2=54549 p3=1
WAIT #9: nam='db file sequential read' ela= 157 p1=477 p2=54550 p3=1
WAIT #9: nam='db file sequential read' ela= 1947 p1=477 p2=54551 p3=1
WAIT #9: nam='db file sequential read' ela= 201 p1=477 p2=54552 p3=1
WAIT #9: nam='db file sequential read' ela= 248 p1=477 p2=54553 p3=1
WAIT #9: nam='db file sequential read' ela= 218 p1=477 p2=54554 p3=1
WAIT #9: nam='db file sequential read' ela= 7529 p1=477 p2=54555 p3=1
WAIT #9: nam='db file sequential read' ela= 256 p1=477 p2=54556 p3=1
WAIT #9: nam='db file sequential read' ela= 205 p1=477 p2=54557 p3=1
WAIT #9: nam='db file sequential read' ela= 164 p1=477 p2=54558 p3=1
WAIT #9: nam='db file sequential read' ela= 2969 p1=477 p2=54559 p3=1
WAIT #9: nam='db file sequential read' ela= 179 p1=477 p2=54560 p3=1
WAIT #9: nam='db file sequential read' ela= 208 p1=477 p2=54561 p3=1
EXEC #9:c=26530000,e=499418724,p=57581,cr=88020,cu=1701909,mis=0,r=1670716,dep=1,og=4,tim=12455600304752
STAT #9 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  '
STAT #9 id=2 cnt=1670716 pid=1 pos=1 obj=800456 op='TABLE ACCESS FULL XXX '
WAIT #3: nam='library cache lock' ela= 199 p1=-4611686015349352232 p2=-4611686015239104728 p3=1301
WAIT #3: nam='library cache lock' ela= 68 p1=-4611686015349352232 p2=-4611686015239104728 p3=1301
=====================
 
……
 
=====================
PARSING IN CURSOR #3 len=408 dep=1 uid=0 oct=2 lid=0 tim=12455600690419 hv=3687727603 ad='caa44558'
insert intocol$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:7,0,null,:7),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)<========和上面一样的更新数据字典表，col$ insert了c_xxx字段！！！
END OF STMT
PARSE #3:c=10000,e=4371,p=0,cr=69,cu=0,mis=1,r=0,dep=1,og=0,tim=12455600690410
BINDS #3:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003f9c000aec0 bln=22 avl=04 flg=05
   value=800456
 bind 1: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=18 oacfl2=8000000100000001 size=32 offset=0
   bfp=c0000000b83ebab2 bln=32 avl=05 flg=09
   value="C_XXX"
 bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003f9c000b778 bln=24 avl=02 flg=05
   value=6
 bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003f9c000b748 bln=24 avl=02 flg=05
   value=6
 bind 4: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003fa0006b668 bln=24 avl=02 flg=05
   value=12
 bind 5: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003fa0006b638 bln=24 avl=02 flg=05
   value=7
 bind 6: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003fa0006b608 bln=24 avl=01 flg=05
   value=0
 bind 7: (No oacdef for this bind)
 bind 8: (No oacdef for this bind)
……

因此，从trace 文件中我们看到，如果alter table TABLE_BIG add column_a date default null;那么其动作和需要的时间分别为：

1. 查找相关数据字典信息，快，等待事件为library cache lock。
2. update新增字段到null，慢，执行计划为全表扫描，执行速度由表的数据量决定，等待事件为db file sequential read。
3. 更新其他数据字典信息，主要是对col$增加新增的字段信息，快，等待事件为library cache lock。

由于我们的要求是新增加字段，且新增字段为空。并且事实上，我们新增字段如果不加default sysdate等这样的信息，仅仅需要是空的就行，因此，在add column时设置default null是没有用的，并且浪费了大量的时间，我们可以去掉它。

附件，本次实验的trace文件下载：

附件1_test_not_default_null.rar

附件2_test_use_default_null.rar

昨晚在对一个省进行打patch时，发现数据库升级脚本执行了很久还没执行完，查看了一下这个执行了很久的语句：

alter table TABLE_BIG add column_a date default null;

类似这样的语句有好几个，而且由于其中涉及到了流水的历史表——一个非常非常大的分区表，执行时间非常的长！其中的一个已经执行了1个半小时还没执行完毕，当时已经凌晨3点半多，按照这样的速度下去，肯定在早上6点前完成不了升级。

检查脚本后决定在数据库层面如下操作：
（1）中断已经在执行的脚本，把后续执行的：alter table TABLE_BIG add column_a date default null;语句中default null去掉，改成：

alter table TABLE_BIG add column_a date ;

这样就只会修改数据字典，不会对大表的每行记录进行null的check。
（2）如果还是会执行很长的时候，在5点前完成不了数据库脚本的升级的话，考虑回滚，中断脚本的执行，且将已经增加字段的表的列set unused（只修改数据字典，很快；若用drop column的方式则很慢），再启动业务。

修改完成后，在不到一分钟的时间内执行完毕了后续的脚本语句！

呵呵，看来加了default null字段确实会在数据库中做不仅仅是更新数据字典的事情，导致执行速度大大变慢。为了弄清楚到底做了些什么操作，我做了个小实验，打了10046的trace出来（level 12的）。（trace全文见文章末尾附件）

不加default null的trace：

……
=====================
PARSING IN CURSOR #1 len=30 dep=0 uid=262 oct=15 lid=262 tim=12456708650157 hv=4259279858 ad='cac40b18'
alter table xxx add c_xxx date <=========操作的语句！！！
……
=====================
PARSING IN CURSOR #11 len=408 dep=1 uid=0 oct=2 lid=0 tim=12456708675572 hv=3687727603 ad='caa44558'
insert intocol$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:7,0,null,:7),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)<========insert col$增加c_xxx字段！！！
END OF STMT
PARSE #11:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=12456708675564
BINDS #11:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0
   bfp=800003fa00071770 bln=22 avl=04 flg=05
   value=800456
 bind 1: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=18 oacfl2=4000000000000001 size=32 offset=0
   bfp=c0000000c5a1694a bln=32 avl=05 flg=09
   value="C_XXX"     <===========此处为insert的值！！！！
 bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0
   bfp=800003fa00071740 bln=24 avl=02 flg=05
   value=6
 bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0
   bfp=800003fa00071710 bln=24 avl=02 flg=05
   value=6
 bind 4: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0
   bfp=800003fa000716e0 bln=24 avl=02 flg=05
   value=12
 bind 5: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacfl
……

加default null的trace：

……
PARSING IN CURSOR #1 len=43 dep=0 uid=262 oct=15 lid=262 tim=12455097007005 hv=944784935 ad='ca96fa30'
alter table xxx add c_xxx date default null  <=========操作的语句！！！
END OF STMT
……
WAIT #10: nam='global cache cr request' ela= 1135 p1=500 p2=47271 p3=-4611686016431196992
FETCH #10:c=0,e=1300,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=3,tim=12455100885697
=====================
PARSING IN CURSOR #9 len=29 dep=1 uid=262 oct=6 lid=262 tim=12455100885938 hv=3942118436 ad='cd491a10'
update "XXX" set "C_XXX"=null   <=========问题就在这里了！！！会在insert col$前update该表，把值update成null！！
END OF STMT
PARSE #9:c=0,e=4059,p=0,cr=5,cu=0,mis=1,r=0,dep=1,og=4,tim=12455100885929
BINDS #9:
WAIT #9: nam='db file sequential read' ela= 73092 p1=480 p2=57738 p3=1
WAIT #9: nam='db file sequential read' ela= 86228 p1=480 p2=57739 p3=1
WAIT #9: nam='db file sequential read' ela= 221129 p1=480 p2=57740 p3=1
……
WAIT #9: nam='db file sequential read' ela= 374 p1=477 p2=54549 p3=1
WAIT #9: nam='db file sequential read' ela= 157 p1=477 p2=54550 p3=1
WAIT #9: nam='db file sequential read' ela= 1947 p1=477 p2=54551 p3=1
WAIT #9: nam='db file sequential read' ela= 201 p1=477 p2=54552 p3=1
WAIT #9: nam='db file sequential read' ela= 248 p1=477 p2=54553 p3=1
WAIT #9: nam='db file sequential read' ela= 218 p1=477 p2=54554 p3=1
WAIT #9: nam='db file sequential read' ela= 7529 p1=477 p2=54555 p3=1
WAIT #9: nam='db file sequential read' ela= 256 p1=477 p2=54556 p3=1
WAIT #9: nam='db file sequential read' ela= 205 p1=477 p2=54557 p3=1
WAIT #9: nam='db file sequential read' ela= 164 p1=477 p2=54558 p3=1
WAIT #9: nam='db file sequential read' ela= 2969 p1=477 p2=54559 p3=1
WAIT #9: nam='db file sequential read' ela= 179 p1=477 p2=54560 p3=1
WAIT #9: nam='db file sequential read' ela= 208 p1=477 p2=54561 p3=1
EXEC #9:c=26530000,e=499418724,p=57581,cr=88020,cu=1701909,mis=0,r=1670716,dep=1,og=4,tim=12455600304752
STAT #9 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  '
STAT #9 id=2 cnt=1670716 pid=1 pos=1 obj=800456 op='TABLE ACCESS FULL XXX '
WAIT #3: nam='library cache lock' ela= 199 p1=-4611686015349352232 p2=-4611686015239104728 p3=1301
WAIT #3: nam='library cache lock' ela= 68 p1=-4611686015349352232 p2=-4611686015239104728 p3=1301
=====================
 
……
 
=====================
PARSING IN CURSOR #3 len=408 dep=1 uid=0 oct=2 lid=0 tim=12455600690419 hv=3687727603 ad='caa44558'
insert intocol$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:7,0,null,:7),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)<========和上面一样的更新数据字典表，col$ insert了c_xxx字段！！！
END OF STMT
PARSE #3:c=10000,e=4371,p=0,cr=69,cu=0,mis=1,r=0,dep=1,og=0,tim=12455600690410
BINDS #3:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003f9c000aec0 bln=22 avl=04 flg=05
   value=800456
 bind 1: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=18 oacfl2=8000000100000001 size=32 offset=0
   bfp=c0000000b83ebab2 bln=32 avl=05 flg=09
   value="C_XXX"
 bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003f9c000b778 bln=24 avl=02 flg=05
   value=6
 bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003f9c000b748 bln=24 avl=02 flg=05
   value=6
 bind 4: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003fa0006b668 bln=24 avl=02 flg=05
   value=12
 bind 5: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003fa0006b638 bln=24 avl=02 flg=05
   value=7
 bind 6: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0
   bfp=800003fa0006b608 bln=24 avl=01 flg=05
   value=0
 bind 7: (No oacdef for this bind)
 bind 8: (No oacdef for this bind)
……

因此，从trace 文件中我们看到，如果alter table TABLE_BIG add column_a date default null;那么其动作和需要的时间分别为：

1. 查找相关数据字典信息，快，等待事件为library cache lock。
2. update新增字段到null，慢，执行计划为全表扫描，执行速度由表的数据量决定，等待事件为db file sequential read。
3. 更新其他数据字典信息，主要是对col$增加新增的字段信息，快，等待事件为library cache lock。

由于我们的要求是新增加字段，且新增字段为空。并且事实上，我们新增字段如果不加default sysdate等这样的信息，仅仅需要是空的就行，因此，在add column时设置default null是没有用的，并且浪费了大量的时间，我们可以去掉它。

附件，本次实验的trace文件下载：

附件1_test_not_default_null.rar

附件2_test_use_default_null.rar

===============================

一个是改数据表结构，一个是结构，同是更新数据