使用索引的误区之四： 空值对索引的影响
** 我们首先做一些测试数据： **
SQL> create table t(x int, y int);
Table created
** 请注意，这里我对表 t 做了一个唯一（联合）索引： **
SQL> create unique index t_idx on t(x,y);
Index created
SQL> insert into t values(1,1);
1 row inserted
SQL> insert into t values(1,NULL);
1 row inserted
SQL> insert into t values(NULL,1);
1 row inserted
SQL> insert into t values(NULL,NULL);
1 row inserted
SQL> commit;
Commit complete
** 下面我们分析一下索引： **
SQL> analyze index t_idx validate structure;
Index analyzed
SQL> select name,lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T_IDX 3
SQL>
然后，我们就可以看到，当前的索引中仅仅保存了 3 行数据。
请注意，上面我们插入并提交了四行数据。
** 所以，这里就有一个结论： **
** Oracle ** ** 的索引不保存全部为空的行。 **
我们继续插入数据，现在再插入几行全部为空的行：
SQL> insert into t values(NULL,NULL);
1 row inserted
SQL> insert into t values(NULL,NULL);
1 row inserted
我们看到这样的插入，居然没有违反前面我们设定的唯一约束（ unique on t(x,y) ），
** 所以，这里我们又得出一个结论： **
** Oracle ** ** 认为 NULL<>NULL ，进而 (NULL,NULL)<>(NULL,NULL) **
** 换句话说， Oracle 认为空值（ NULL ）不等于任何值，包括空值也不等于空值。 **
我们看到下面的插入会违反唯一约束 (DEMO.T_IDX) ，这个很好理解了，因为它不是全部为空的值，即它不是（ NULL,NULL ），只有全部为空的行才被认为是不同的行：
SQL> insert into t values(1,null);
insert into t values(1,null)
ORA-00001: 违反唯一约束条件 (DEMO.T_IDX)
SQL> insert into t values(null,1);
insert into t values(null,1)
ORA-00001: 违反唯一约束条件 (DEMO.T_IDX)
SQL>
请看下面的例子 :
SQL> select x,y,count(*) from t ** group by x,y; **
** X Y COUNT(*) **
** ----- -------- ---------- **
** 3 **
** 1 1 **
** 1 1 **
** 1 1 1 **
Executed in 0.03 seconds
SQL> select x,y,count(*) from t where x is null and y is null ** group by x,y; **
** X Y COUNT(*) **
** ---- ------- ---------- **
** 3 **
Executed in 0.01 seconds
SQL>
SQL> select x,y,count() from t ** group by x,y ** having count()>1;
** X Y COUNT(*) **
** ------ -------------------- ---------- **
** 3 **
Executed in 0.02 seconds
SQL>
** 可以看见，完全为空的行有三行，这里我们又可以得出一个结论： **
** oracle ** ** 在 group by 子句中认为完全为空的行是相同的行 **
** 换句话说，在 group by 子句中， oracle 认为 (NULL,NULL)=(NULL,NULL) **
下面的语句，使用了复合索引（ x,y ）的前导列，通常这样的查询是会使用索引的，我们看看下面的例子：
select * from t ** where x is null ** ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS FULL | T | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."X" IS NULL)
Note: rule based optimization
14 rows selected
Executed in 0.06 seconds
** 我们看到上面的查询并没有使用索引，那么对比一下不使用控制的情况： **
** 对比一下下面的查询： **
select * from t ** where x=1; **
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | INDEX RANGE SCAN | T_IDX | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."X"=1)
Note: rule based optimization
14 rows selected
Executed in 0.04 seconds
这个查询（ ** where x=1 ** ）如我们所希望的那样使用了 t_idx(x,y) 复合索引，这里我们可以得出一个结论：
** 在使用 IS NULL 和 IS NOT NULL 条件的时候， Oracle 不使用索引（因为 Oracle 的索引不存储空值，详细请参见前面的相关内容） **
那么我们如何使用空值的比较条件呢？
首先，尽量不在前导列上使用空值，请看下面的例子：
select * from t where x=1 and y is null;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | INDEX RANGE SCAN | T_IDX | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."X"=1)
filter("T"."Y" IS NULL)
Note: rule based optimization
15 rows selected
select * from t where x is null and y=1;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS FULL | T | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."Y"=1 AND "T"."X" IS NULL)
Note: rule based optimization
14 rows selected
还有一个可以变通的方法，即我们在创建表的时候，为每个列都指定为非空约束（ NOT NULL ），并且在必要的列上使用 default 值，如：
SQL> create table lunar(
2 c 1 varchar2(10) default 'empty'
3 constraint c1_notnull not null,
4 c 2 number(10) default 0
5 constraint c2_notnull not null,
6 c 3 date default to_date('20990101','yyyymmdd')
7 constraint c3_notnull not null);
表已创建。
已用时间 : 00: 00: 00.00
SQL> insert into lunar(c1) values('first');
已创建 1 行。
已用时间 : 00: 00: 00.00
SQL> insert into lunar(c2) values(99);
已创建 1 行。
已用时间 : 00: 00: 00.00
SQL> insert into lunar(c3) values(sysdate);
已创建 1 行。
已用时间 : 00: 00: 00.00
SQL> insert into lunar(c1,c3) values('ok',sysdate);
已创建 1 行。
已用时间 : 00: 00: 00.00
SQL> insert into lunar(c2,c1) values(999,'hello');
已创建 1 行。
已用时间 : 00: 00: 00.00
SQL> commit;
提交完成。
已用时间 : 00: 00: 00.00
SQL> select * from lunar;
C 1 C 2 C 3
---------- ---------- ----------
first 0 01-1 月 -99
<P class=MsoPla