add column时default null的问题

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

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

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

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

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

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

不加default null的trace:

加default null的trace:

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

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

附件,本次实验的trace文件下载:

附件1_test_not_default_null.rar

附件2_test_use_default_null.rar

相关文章

一条评论

  1. add column的时候,新加的column默认应该就是null的吧?加default null是不是多余了?
    另外,update “XXX” set “C_XXX”=null的时候为什么是’db file sequential read’事件?正常情况下此时应该是’db file scattered read’才对。是否是’file sequential read’导致执行效率低下?

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.