书接上文:薛定谔的猫是如何诞生的?

编辑手记:注重细节,是DBA必要的基本素质要求。

书接上文(参考:空与非空 - 数据库中也有薛定谔的猫?),其实CBO的判断本身是没有问题的,问题在于,为什么一个空值会存在非空约束的字段中。

SQL> select dbms_metadata.get_ddl('TABLE', 'T_DEF') from dual; DBMS_METADATA.GET_DDL('TABLE','T_DEF') ---------------------------------------------------------------------- CREATE TABLE "TEST"."T_DEF" ( "ID" NUMBER, "NAME" VARCHAR2(8) DEFAULT 'a', "TYPE" VARCHAR2(8) DEFAULT '' NOT NULL ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

之前提到,由于TYPE列具有非空约束,导致CBO给出的执行计划返回了错误的结果,但是问题的根源在于,为什么Oracle会允许空值插入到非空约束字段中:

SQL> insert into t_def (id, name) values (1, 'a'); insert into t_def (id, name) values (1, 'a') * ERROR at line 1: ORA-01400: cannot insert NULL into ("TEST"."T_DEF"."TYPE")

那么是什么情况导致了错误的数据绕过了Oracle的检查呢。检查表的定义,发现一个特别之处,TYPE列的默认值本身就是NULL,是不是这个导致了Oracle的数据问题呢:

SQL> CREATE TABLE T_TEST (ID NUMBER, NAME VARCHAR2(30) DEFAULT '' NOT NULL); 表已创建。 SQL> INSERT INTO T_TEST (ID) VALUES (1); INSERT INTO T_TEST (ID) VALUES (1) * 第 1 行出现错误: ORA-01400: 无法将 NULL 插入 ("TEST"."T_TEST"."NAME")

显然问题没有那么简单,虽然默认值人为设置为NULL并不常见,但是对于哪些具有NOT NULL约束且没有指定默认值的列,都相当于默认值为NULL。显然不太可能是常规问题导致的bug,Oracle经过这么多年这么多版本的磨练,应该不会在11g还出现这种问题,而且这个问题还是第一次碰到。综上所述,推断问题可能是11g新特性所引入的bug。

分析到这里,问题的答案也呼之欲出了,没错,导致问题的就是11g新增的快速添加非空默认值的功能,这个诡异的问题可以通过下面的三步简单的重新:

SQL> create table t_def (id number, name varchar2(30) default '' not null); Table created. SQL> insert into t_def values (1, 'a'); 1 row created. SQL> alter table t_def add type varchar2(8) default '' not null; Table altered. SQL> select * from t_def; ID NAME TYPE ---------- ------------------------------ -------- 1 a

Oracle确实允许NOT NULL列的默认值为NULL,如果不指定默认值那么就相当于默认值为NULL,但是对于11g新增的新特性而言,DEFAULT为NULL是要禁止的,否则就会导致现有记录的NOT NULL字段出现NULL值。

而且由于指定的DEFAULT是NULL,ECOL$中居然没有记录任何信息:

SQL> select * from sys.ecol$; no rows selected

看来任何新特性都难以避免BUG的产生,没想到一个增加非空默认值的新特性也会引发BUG。

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

原文发表时间:2016-07-13

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏idba

MySQL 各种SQL语句加锁分析

Locking read( SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE),UPDATE以及DE...

1022
来自专栏Java帮帮-微信公众号-技术文章全总结

【数据库】MySQL进阶一、主外键讲解

MySQL进阶主外键讲解 1.什么是外键: 主键:是唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性 外键:是另一表的主键, 外键可...

3507
来自专栏乐沙弥的世界

MongoDB 稀疏(间隙)索引(Sparse Indexes)

a、间隙索引就是创建索引的索引列在某些文档上列不存在,导致索引存在间隙。 b、间隙索引在创建时应指定选项:{ sparse: true } c、间隙索引...

824
来自专栏企鹅号快讯

python数据处理实战-自动统计mysql数据库数据表每天数据量

日常报表统计,日总量,日增量不可避免,这篇文章我们从实际应用出发,从逻辑思考到最后写出代码,一步步分析拆解 一.表结构设计 既然想统计每一张表每天的数据量,后续...

4457
来自专栏杨建荣的学习笔记

走索引扫描的慢查询(r3笔记45天)

今天查看awr报告的时候,发现一条sql语句异常。 Elapsed Time (s) Executions Elapsed Time per Exec (s)...

3668
来自专栏黑泽君的专栏

在命令行下,Mysql显示各个端所使用的字符集命令

662
来自专栏乐沙弥的世界

Oracle 测试常用表BIG_TABLE

创建测试用表,DBA经常用到,通常都是基于dba_objects来创建的比较多。本文根据Tom大师的big_table进行了整理,供大家参考。

1062
来自专栏杨建荣的学习笔记

关于奇怪的并行进程分析(三)(r6笔记第47天)

在前两篇的基础上,对于一个环境中存在的奇怪并行进程问题进行了初步的分析。 初步排除了是通过scheduler的job运行导致的,一方面因为运行的时间会有延迟,甚...

2774
来自专栏杨建荣的学习笔记

关于pl/sql中的绑定变量(r3笔记第73天)

在看关于shared pool的文档时,必定会提到绑定变量,也能够通过几个简单的例子对绑定变量带来影响有深刻的认识,但是在工作中,可能有时候我们就忘了绑定变量的...

2584
来自专栏Python

异常处理:1215 - Cannot add foreign key constraint

  最近在做新生入学系统,学生表中包括新生的班级,专业等信息,班级,专业就需要和班级表,专业表进行关联,但是在添加外键的过程中却出现了“Cannot add f...

22610

扫码关注云+社区