非空字段空值对查询的影响

BLOG有三年没有更新了，其实这篇文档也是三年前写的，不过当时没有贴出来，今天看到有人问起，于是分享出来。

这是客户数据库中碰到的一个有意思的问题。
数据库版本为11.2.0.3，客户的DSG同步软件在同步某张表时出现了错误，报错无非插入空值ORA-1400，本来这是一个很简单的问题，但是诊断发现问题比较有意思：
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
第一个查询不能反映任何问题，而第二个查询和第三个查询明确的告诉我们，TYPE列的值不是NULL。但是第四个查询和第五个查询又确定告之我们，TYPE列是NULL。到底TYPE列的值是NULL还是NOT NULL呢，这是一个问题。
Oracle给了我们两个不同的答案，但是可以坚信的是，一条记录的字段不可能同时具有这两种属性，要不然是NULL值，要不然就是NOT NULL，它不可能既是NULL又是NOT NULL，这不科学。什么？薛定谔的猫？谁说的？我的刀呢？！
电子的波粒二象性问题本质是波动和粒子性不想人们一开始认为的那样是完全对立的，事实上在电子的尺度上，本身二者就是共存的，所以你用检查波动的方法去观察电子，电子就以波的形式出现，而以粒子方式去观察电子，电子就以粒子的方式呈现。因此，波粒二象性本质并不是对立的。
薛定谔的猫的本质也是相同的，当然还要涉及到意识、观测、坍塌等一系列复杂的问题。建议不了解量子力学的同学们直接跳过以上两段内容。而如果你对量子力学有所研究，你就知道我完全是做为一个门外汉在胡扯，请无视上面两段内容。
好了，扯淡完毕，回到Oracle的问题上，我个人不相信量子尺度上的不确定性会影响到Oracle的查询结果，因此我们需要找到问题出在哪里。
简单分析一下，通过WHERE条件判断得到的字段空值与否与通过函数得到的结果是相反的。在宏观的维度上看，那么总有一个结果是错误的。既然Oracle本身已经不可信了，那么我们来看看数据本身到底是如何的。
SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t_def;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 173109
SQL> alter system dump datafile 4 block 173109;
System altered.
下面看一下数据块中的内容：
Block header dump: 0x0102a435
Object id on Block? Y
seg/obj: 0x145df csc: 0x00.23b6097 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x102a430 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.01b.00001afc 0x00c026de.11be.10 --U- 1 fsc 0x0000.023b6098
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0102a435
data_block_dump,data header at 0x7fe7fdc97264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7fe7fdc97264
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f90
avsp=0x1f7b
tosp=0x1f7b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f90
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 173109 maxblk 173109
显然，数据块中只有两个列的值，也就是真实的情况是TYPE列是NULL。在WHERE条件和函数的PK中，函数取得了最终的胜利。
但是对我们而言，问题才刚刚开始，为什么明明是NULL值，但是通过IS NULL和IS NOT NULL的查询条件进行过滤，得到的是与真实条件相反的结果。
Oracle的执行语句不会这么笨，连最基本的查询条件都判断错吧。事实上，导致查询结果相左的一个原因恰恰是Oracle的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"
SQL> set autot on exp
SQL> select * from t_def where type is null;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 177263571
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_DEF | 1 | 5 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):,F
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
SQL> select * from t_def where type is not null;
ID NAME TYPE
---------- -------- --------
1 a
Execution Plan
----------------------------------------------------------
Plan hash value: 1057129282
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_DEF | 1 | 5 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
现在我们可以放心大胆的宣布，这个问题和猫没有关系。从表的定义可以看到，TYPE列的属性是NOT NULL的，而目前记录中TYPE的值是NULL，这就是问题的关键。
从10g开始，CBO变得更聪明，但一个查询条件明显的违反表中的约束条件时，CBO会在执行计划的最上层增加一个FILTER，而FILTER的条件是恒为假的条件NULL IS NOT NULL，导致的结果是Oracle根本不需要真正执行这个语句，就直接返回0条记录，因为表中定义的限制条件是TYPE非空，Oracle并不会去执行这个查询语句，而是直接返回了0条记录。
而对于TYPE IS NOT NULL的查询而言，由于查询条件满足约束的条件，因此Oracle在全表扫描后省略掉了原本应该做的TYPE IS NOT NULL的过滤，而直接将结果返回给用户，造成了TYPE IS NOT NULL条件返回的结果是NULL的情况。
简单的说，导致这个问题的原因是由于错误的数据存储于表中，而这导致了CBO在判断时出现了错误，导致和预期相反的结果返回。