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

编辑手记:注重细节,是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 条评论
登录 后参与评论

相关文章

来自专栏数据库新发现

Oracle的X$表系列介绍之-X$KSLLCLASS

« HRAY纳斯达克的IPO历程 | Blog首页 | Windows Xp中如何设置自动登录 »

763
来自专栏iMySQL的专栏

分区表场景下的 SQL 优化

有个表做了分区,每天一个分区。该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗?

750
来自专栏Netkiller

Springboot @RequestBody 传递 List

本文节选自电子书《Netkiller Java 手札》 6.1.4.1. @RequestBody 传递 List package cn.netkille...

35910
来自专栏数据和云

SQL优化之一则MySQL中的DELETE、UPDATE 子查询的锁机制失效案例

开发与维护人员避免不了与 in/exists、not in/not exists 子查询打交道,接触过的人可能知道 in/exists、not in/not e...

953
来自专栏性能与架构

体验 MySQL 8.0 JSON聚合函数

MySQL 最近的动作很快,已经计划推出 8.0 版本,会新增很多新特性 在 5.7 中,JSON 已经被正式支持,但在 SQL 中对 JSON 的处理能力较弱...

38816
来自专栏后台日记

Mysql INSERT ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE是MySQL insert的一种扩展。当发现有重复的唯一索引(unique key)或者主键...

1223
来自专栏james大数据架构

你真的会玩SQL吗?简单的数据修改

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

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

一个SQL语句引发的ORA-00600错误排查(二)(r9笔记第65天)

继昨天一个SQL语句导致的ORA-00600错误之后,我给出了背景和初步的分析结果,今天来给出我的结论,当然说明原因不是我的本意,还有反思。 首先语句类似这样的...

3397
来自专栏个人随笔

MySQL高级查询

 高级查询     关键字书写顺序  关键字执行顺序 select:投影结果       1    5 from:定位到表             2   ...

4119
来自专栏数据和云

循序渐进:Oracle 12.2的Sharding基础概念解读

张大朋(Lunar)Oracle 工程师 Lunar 拥有超过十年的 ORACLE SUPPORT 从业经验,曾经服务于ORACLE ACS部门,现就职于 O...

2644

扫描关注云+社区