使用索引的誤區之四：空值對索引的影響
我們首先做一些測試數據：
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 c1 varchar2(10) default 'empty'
3 constraint c1_notnull not null,
4 c2 number(10) default 0
5 constraint c2_notnull not null,
6 c3 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;
c1 c2 c3
---------- ---------- ----------
first 0 01-1月 -99
empty 99 01-1月 -99
empty 0 19-10月-04
ok 0 19-10月-04
hello 999 01-1月 -99
已用時間: 00: 00: 00.00
sql> select c1,c2,to_char(c3,'yyyy-mm-yy') from lunar;
c1 c2 to_char(c3
---------- ---------- ----------
first 0 2099-01-99
empty 99 2099-01-99
empty 0 2004-10-04
ok 0 2004-10-04
hello 999 2099-01-99
已用時間: 00: 00: 00.00
sql>
然后我們再像使用一般的列那樣，使用他們，并且合理的為他們建立索引相信就可以很好的提高應用的查詢效率。
