索引与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不起作用

我们做一系列与单列索引时类似的测试：
测试一、suk@ORACLE9I> 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不起作用

测试二、suk@ORACLE9I> 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)

--指明组合索引的其中一个列不为空后可以走索引

测试三、suk@ORACLE9I> 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)

--指明组合索引其中一列为某一个值后可以走索引

测试四、suk@ORACLE9I> 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看成一个常数）

测试五、suk@ORACLE9I> 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的值后可以走索引

测试六、suk@ORACLE9I> 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

suk@ORACLE9I> 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约束可以走索引

测试九、suk@ORACLE9I> alter table t modify b null;

表已更改。

suk@ORACLE9I> alter table t modify c not null;

表已更改。

suk@ORACLE9I> 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 也可以用到索引

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-63751/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-63751/