【云和恩墨大讲堂】谈Oracle表新增字段的影响

作者简介

刘晨,网名bisal,Oracle 10g/11g OCM,并国内首批Oracle YEP成员,博客:blog.itpub.net/bisal

很多人在做一些表设计时会留出几个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新增默认值非空字段使用数据字典存储的特性,并且做了扩展支持,满足范围更大了。可以说,小问题隐藏了大智慧。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-01-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java成神之路

Mybatis_总结_03_用_动态SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要...

312
来自专栏孙银行的专栏

当谈 SQL 优化时谈些什么?

Mysql数据库作为数据持久化的存储系统,在实际业务中应用广泛。在应用也经常会因为SQL遇到各种各样的瓶颈。增删改查等操作最经常遇到的问题是“查”,查询又以索引...

2.8K2
来自专栏Kevin-ZhangCG

Oracle学习笔记一

Oracle数据库是数据的物理存储。这就包括(数据文件ORA或者DBF、控制文件、联机日志、参数文件)。其实 Oracle数据库的概念和其它数据库不一样,这里...

722
来自专栏数据分析

[数据库基础]——索引

一、引言 对数据库索引的关注从未淡出我的们的讨论,那么数据库索引是什么样的?聚集索引与非聚集索引有什么不同?希望本文对各位同仁有一定的帮助。有不少存疑的地方,...

3227
来自专栏跟着阿笨一起玩NET

sql server 获取每一个类别中值最大的一条数据

SELECT  * FROM    (           SELECT    * ,                     ROW_NUMBER() OVE...

381
来自专栏互联网开发者交流社区

Oracle-函数大全

1165
来自专栏用户2442861的专栏

游标--数据库

http://blog.csdn.net/liujiahan629629/article/details/18014051

813
来自专栏Python

表的数据类型

一 介绍 存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的 详细参考: http://www.runoob....

1797
来自专栏工科狗和生物喵

【计算机本科补全计划】Mysql 学习小计(2)

正文之前 昨天下午写了篇 Mysql学习小计,结果出乎意料的受欢迎?变相刺激了我多写点 Mysql?好吧,如尔所愿。我晚上反正还不知道学点啥,就把今天看的那个菜...

34311
来自专栏性能与架构

Mysql Join的实现原理

在MySQL中,只有一种Join算法,就是大名鼎鼎的NestedLoop Join 对左表进行遍历,拿一条数据和右表的每条数据进行比对,如果找到N条匹配的,此条...

3166

扫描关注云+社区