新增非空约束字段在不同版本中的演进

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 新增非空约束字段在不同版本中的演进

开发提了一个数据库变更需求,新增一字段,没有NOT NULL非空约束,但有默认值为NULL。看起来有些奇怪,因为若字段允许NULL,其默认值就是NULL,不用显示声明,可以创建一个无DEFAULT NULL的新增字段再查看desc表结构,就可以证明这点。

基于这问题,引申出的NOT NULL字段问题还有不少,也是比较容易忽视的一些细节,例如杨长老最近连续发表过两篇关于NOT NULL字段的文章确实很有启发,

非空字段空值对查询的影响:http://yangtingkun.net/?p=1481

非空字段空值的产生:http://yangtingkun.net/?p=1483

具体各位可以参考这两篇博文,简单总结一下,11.2.0.3的库, 

1.使用where type is null和is not null得到的记录结果判断值为非空。 

2.使用dump(type)和nvl(type, ‘is null’)得到的记录结果判断值为空。 
表定义中此字段为DEFAULT ” NOT NULL,事实证明(2)是正确的,之所以有(1)的结论,原因是CBO太智能了。 
对于IS NOT NULL,type字段定义为NOT NULL,此SQL明显违反了表中的约束条件,则会在执行计划最上层增加一个NULL IS NOT NULL恒为假的条件,根本不需要真正执行这个SQL,直接返回0条记录。 
对于IS NULL,由于查询条件满足约束的条件,因此Oracle会做全表扫描,并且省略了type is not null的过滤,直接返回所有记录,就造成了type非空的假象。 
出现以上问题的核心,还是为何有为空的记录存储于有NOT NULL非空约束的表中。原因就是11g新特性,新增一个有默认值的NOT NULL约束的字段,默认值不会像以前一样,插入每条记录中,而是会存储于一张数据字典表sys.ecol$,Oracle允许NOT NULL列默认值为NULL,因此对于11g来说,需要禁止DEFAULT为NULL的这种行为。

这种新增非空约束字段在不同版本中确实有一些细节的变化,下面做一些简单测试。 
首先,创建测试表,插入一条数据,新增列为NOT NULL且默认值是”的字段:

create table bisal (id number);

insert into bisal values(1);

alter table bisal add name varchar2(10) default ‘’ not null;

10.2.0.3库,从报错信息看ORA-01407,不能更新NAME列为空,可以看出此时是要将表中已存在记录的新列name做UPDATE设置为默认值的操作,由于有非空约束,因此不允许。 

新增非空约束字段在不同版本中的演进

11.2.0.1库,可以新增字段,表中已存记录该值确实为空,即允许一个有NOT NULL约束的字段包含NULL值。 
新增非空约束字段在不同版本中的演进 
关于这新特性的好处,可以参考之前写的 
《alter table新增字段操作究竟有何影响?(上下篇)》 
http://blog.csdn.net/bisal/article/details/45418303 
http://blog.csdn.net/bisal/article/details/49182025

12.1.0.2库,我们可以看出和10g一样,禁止新增一个默认值为NULL的NOT NULL约束字段,但报错信息变了,ORA-01758: table must be empty to add mandatory (NOT NULL) column,这个错误号在之前的版本有定义,不是新号。 
新增非空约束字段在不同版本中的演进
根据错误提示,我们删除表中数据,再新增字段,可以增加,但不能再插入一条NULL至这个非空约束字段。 
新增非空约束字段在不同版本中的演进

我们再看下官方文档的描述,11g中对于新增默认值字段的描述部分,明确指出NOT NULL约束包含默认值的情况下,是将默认值存储于数据字典中。 
新增非空约束字段在不同版本中的演进

12c中描述允许为空的字段,若有默认值,不会更新已存数据,而是会借助数据字典完成存储,这种新特性的适用范围更广了。 
新增非空约束字段在不同版本中的演进
由于我没有12c的sys真实环境,朋友可以自己尝试下,有机会我会自己再验证下。

至此,12c修复了11g中这个非空约束字段允许保存空值的bug,同时又支持11g新增默认值非空字段使用数据字典存储的特性,并且做了扩展支持,满足范围更大了。

小问题隐藏了大智慧。

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 新增非空约束字段在不同版本中的演进


 上一篇
一个低级的ORA-01017错误 一个低级的ORA-01017错误
事件缘由: 使用sys账户创建了一个数据清理的存储过程,再创建一个Oracle job定时运行这个存储过程,用于做表数据的清理。第二天看表数据未删除,说明job执行有错,打算使用sys账号登录查看job运行情况,反复输入sys账户信息,总提
下一篇 
Oracle Patch补丁体系和如何打补丁 Oracle Patch补丁体系和如何打补丁
Oracle作为大型商用关系型数据库,从其补丁体系就可以看出其考虑的全面性。首先我们看下Oracle Patch的主要类型[参考1和2]: Version/维护版本 针对前一个维护版本的所有补丁进行整理,增加新的功能或对软件有较大的改动,进