索引與null（一）：單列索引

這篇文章主要討論單列索引與null的關係。

一、單列索引
SQL> create table t(a int,b int);

Table created

SQL> create index idx_t on t(a);

Index created

SQL> select * from t;

A B
---------- ----------
1 1
2 2
3

SQL> analyze index idx_t compute statistics;

Index analyzed

1、單列是否索引null
SQL> select index_name,NUM_ROWS,DISTINCT_KEYS from user_indexes where index_name='IDX_T';

INDEX_NAME NUM_ROWS DISTINCT_KEYS
------------------------------ ---------- -------------
IDX_T 2 2

從以上可以看出，索引不索引null值，只索引了a=1和a=2的值
也可以用把索引所在block dump出來檢視索引情況（索引所在檔案位置可由dba_extents的file_id,block_id確定）
trace檔案主要部分如下：
-----------------------------------------------------
row#0[8024] flag: -----, lock: 2
col 0; len 2; (2): c1 02 --這是列值
col 1; len 6; (6): 01 80 79 b2 00 00 --這是rowid
row#1[8012] flag: -----, lock: 2
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 79 b2 00 01
-----------------------------------------------------
我們可以透過如下方法驗證：
SQL> select to_number('c1','xxx'),to_number('02','xxxx'),to_number('c1','xxx'),to_number('03','xxxx') from dual;

TO_NUMBER('C1','XXX') TO_NUMBER('02','XXXX') TO_NUMBER('C1','XXX') TO_NUMBER('03','XXXX')
--------------------- ---------------------- --------------------- ----------------------
193 2 193 3

SQL> select dump(1),dump(2) from dual;

DUMP(1) DUMP(2)
------------------ ------------------
Typ=2 Len=2: 193,2 Typ=2 Len=2: 193,3

可以看出，只有a=1和a=2的記錄被索引了。

2、為什麼hints不起作用

> select * from t;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=18)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=2 Bytes=18)

我們在某些時候需要使用hints來調整sql的執行計劃，比如我們可以使用/*+ index(table_name index_name) */來強制使用索引：

> select /*+ index(t idx_t) */ * from t;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=2132
)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=82 Bytes=2132)

> select /*+ index(t idx_t) */ * from t where b=2;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=26)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=26)

這個執行計劃與我們預期不一樣，它並沒有按照hints使用索引來存取資料。這是為什麼呢？
上面的第一個sql的目的是返回t表的所有資料，第二個是要返回滿足條件b=2的所有資料。我們知道，B*tree索引是不會null值的，在以上的查詢中如果用索引的話，無論是直接從索引返回資料，還是根據索引掃描進而透過rowid獲取資料，都無法保證返回資料的正確性。oracle意識到這一點，忽略了強制使用索引的hints，透過全表掃描來返回資料。

3、用hints強制使用索引的前提

上文我們知道，是因為a列存在null值導致不能使用索引，那麼我們把a is null的記錄刪除了，是否可以使用hints呢？> delete from t where a is null;

已刪除 1 行。

> commit;

提交完成。

> select /*+ index(t idx_t) */ * from t;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=2132
)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=82 Bytes=2132)

從執行計劃看出，即使表t的a列已經不存在null的情況，但仍然使用了全表掃描。這是因為oracle並不知道a列是否會存在null的情況（oracle不可能做到每次執行都遍歷一次表的資料檢視資料的分佈情況），為了保證返回資料的正確行，oracle選擇了全表掃描。
我們接著看：

> select /*+ index(t idx_t) */ * from t where a is not null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=18)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=2 Bytes=
18)

2 1 INDEX (FULL SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=2
)

現在的執行計劃已經如我們所願，走索引了。這是因為我們限定了a不能為null，這樣就可以保證使用索引就可以返回所有需要的資料。

接著看：> select /*+ index(t idx_t) */ * from t where a=1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
9)

2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=
1)

執行計劃也可以走索引了，因為設定了a=1，已經限定了a肯定是一個非空的值了，可以從索引返回資料了。

再考察兩個例子：> select /*+ index(t idx_t) */ * from t where a<>1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
9)

2 1 INDEX (FULL SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=1
)

> select /*+ index(t idx_t) */ * from t where a=null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
9)

2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=
1)

在oracle中，null=null和null<>null都不成立，null不能與任何值作大小比較。
由於null的這個特殊性，在條件a<>1中，已經忽略了a is null的情況，相當於已經隱含了a is not null條件了；
由於判斷某一個列是否為null只能用is [not] null，在a=null中，這裡的null被看作是一個常數（雖然它永遠為false），所以這個條件除了返回資料不一樣以外，其它作用與a=1是一樣的，也可以透過索引來獲取所有符合條件的資料。

再看下一個例子：

> alter table t modify(a not null);

表已更改。

> select /*+ index(t idx_t) */ * from t;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=18)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=2 Bytes=
18)

2 1 INDEX (FULL SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=2
)

我們對錶t的a列加上一個not null約束，orace知道索引會包含了a列的所有值，用索引可以返回所有符合條件的資料，索引hints起作用了。

從上面的例子可以看出，B*TREE索引不會索引null是整個問題的關鍵。因此，如果想要使用索引掃描，甚至是強制用/*+ index(table_name index_name) */強制使用索引，必須滿足以下三個條件中的一個或者多個：
（1）在想要用到的索引對應的欄位上有not null約束就可以用到索引。如果索引列有not null約束，無論條件怎麼寫都可能用到索引（包括col is null）
（2）如果索引列沒有not null約束，在where條件中限定了用到的索引對應的列為not null可以用到索引。如col1 is not null
（3）如果索引列沒有not null約束，且在條件中沒有指明索引列為not null，則在where條件中包含了用到的索引對應的列與其他值的比較（除col1 is null外）可以用到索引。假如索引對應列為a，則包含以下條件的查詢都可能用到索引：
a=b/a<>b/a>b/a