索引與null（二）：組合索引

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

二、組合索引

SQL> create table t(a int,b int,c int);

Table created

SQL> create unique index idx_t on t(b,c);

Index created

SQL> select * from t;

A B C
---------- ---------- ----------
1 1 1
2 2
3 3
4

SQL>
1、索引是否索引null值

SQL> analyze table t compute statistics for table for all indexes for all indexed columns;

Table analyzed

SQL> select index_name,table_name,num_rows from user_indexes where table_name='T';

INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
IDX_T T 3

--索引包含了3行，顯然，欄位不全為空的行可以被索引。
把索引的block dump出來，主要部分如下：
-------------------------------------------------------------------
row#0[8022] flag: -----, lock: 0, data:(6): 01 80 74 52 00 00
col 0; len 2; (2): c1 02
col 1; len 2; (2): c1 02
row#1[8010] flag: -----, lock: 0, data:(6): 01 80 74 52 00 01
col 0; len 2; (2): c1 03
col 1; NULL
row#2[7998] flag: -----, lock: 0, data:(6): 01 80 74 52 00 02
col 0; NULL
col 1; len 2; (2): c1 04
------------------------------------------------------------------
--可見，唯一性索引並不包含rowid

2、null和null是否相等

SQL> select * from t;

A B C
---------- ---------- ----------
1 1 1
2 2
3 3
4

SQL> insert into t values(5,2,null);

insert into t values(5,2,null)

ORA-00001: 違反唯一約束條件 (SUK.IDX_T)

--在(b,c)上建有唯一性索引，這裡違反了唯一性約束是不是表示(2,null)=(2,null)，也就是null=null呢？其實這樣理解是錯誤的，在oracle中，null=null、null<>null永遠都不會成立，oracle在處理唯一性約束時也不例外。在這裡例子中，null=null只是表象，從這個表象得到的結論是錯誤的。（）
--我們知道，oracle索引不索引空值，唯一行索引索引值由列值組成，不包含rowid，在(2,null)這行中，索引的鍵值是2(null不被索引)，如果再查入一行(2,null)，鍵值也為2，違反了唯一性所以約束，也就是上例報錯的真正原因。
--實際上，在唯一性組合索引中，如果兩行中相同的列的值相同，則會違反唯一性約束，如：
(2,null)和(2,null)會違反唯一性約束，但
(2,null)和(null,2)不會違反唯一性約束。

3、為什麼hints不起作用

我們做一系列與單列索引時類似的測試：
測試一、> select /*+ index(t idx_t) */ * from t;

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

--hint不起作用

測試二、> select /*+ index(t idx_t) */ * from t where b 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' (UNIQUE) (Cost=1 Card=2)

--指明組合索引的其中一個列不為空後可以走索引

測試三、> select /*+ index(t idx_t) */ * from t where b=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' (UNIQUE) (Cost=1 Card=1)

--指明組合索引其中一列為某一個值後可以走索引

測試四、> select /*+ index(t idx_t) */ * from t where c=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' (UNIQUE) (Cost=1 Card=2)

--指明組合索引的其中一列為某一個值後可以走索引（這裡把null看成一個常數）

測試五、> select /*+ index(t idx_t) */ * from t where b=1 and c=1;

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

2 1 INDEX (UNIQUE SCAN) OF 'IDX_T' (UNIQUE)

--同時指定b、c的值後可以走索引

測試六、> select /*+ index(t idx_t) */ * from t where b is null and c is not 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' (UNIQUE) (Cost=1 Card=1)
--指定組合索引中的一列為空，另一列不能為空，可以走索引

測試八、
SQL> delete from t where b is null or c is null;

3 rows deleted

SQL> alter table t modify b not null;

Table altered

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

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

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

--為組合索引的其中一個欄位加上not null約束可以走索引

測試九、> alter table t modify b null;

表已更改。

> alter table t modify c not null;

表已更改。

> select /*+ index(t idx_t) */ * from t where b is null and c is 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 (RANGE SCAN) OF 'IDX_T' (UNIQUE) (Cost=1 Card=2)

--組合索引的其中一個欄位限定了不能為空，即使條件都是is null也可以走索引
--oracle在執行sql的時候會檢視錶的設計，然後得到的表的結構資訊忽略一些無效的調教，上例就是這樣。
--這也說明了網上常說的col is null不能用到索引的說法是不完全準確的，在某些條件下，col is null也可以用到索引。

4、用hints強制使用索引的前提
假如有組合索引(a,b)：
（1）在想要用到的組合索引對應的其中一個或者多個欄位上有not null約束就可以用到索引。如果索引列其中一個或者多個有not null約束，無論條件怎麼寫都可能用到索引（包括a is null and b is null）
（2）如果索引列沒有not null約束，在where條件中限定了用到的索引對應的其中一個或者多個列為not null就可以用到索引，如a is not null（甚置 a is not null and b is null 也可以用到索引）
（3）如果索引列沒有not null約束，且在條件中沒有指明索引列為not null,在where條件中包含了用到的索引對應的一個或者多個列與其他值的比較（除a is null外）可以用到索引。甚至 a=1 and b is null 也可以用到索引