oracle 非空默认值,非空字段空值的产生

上一篇讨论了非空字段中如果存在空值对于查询的影响，这里描述一下导致问题的原因。

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

书接上文，其实CBO的判断本身是没有问题的，问题在于，为什么一个空值会存在非空约束的字段中。

重新看一下问题：

SQL> select * from t_def;

ID NAME TYPE

---------- ------------------------------ --------

1 a

SQL> select * from t_def where type is null;

no rows selected

SQL> select * from t_def where type is not null;

ID NAME TYPE

---------- ------------------------------ --------

1 a

SQL> select dump(type) from t_def;

DUMP(TYPE)

--------------------------------------------

NULL

SQL> select nvl(type, 'is null') from t_def;

NVL(TYPE

--------

is null

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。