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

suk@ORACLE9I> 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) */来强制使用索引：

suk@ORACLE9I> 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)

suk@ORACLE9I> 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呢？suk@ORACLE9I> delete from t where a is null;

已删除 1 行。

suk@ORACLE9I> commit;

提交完成。

suk@ORACLE9I> 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选择了全表扫描。
我们接着看：

suk@ORACLE9I> 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，这样就可以保证使用索引就可以返回所有需要的数据。

接着看：suk@ORACLE9I> 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肯定是一个非空的值了，可以从索引返回数据了。

再考察两个例子：suk@ORACLE9I> 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
)

suk@ORACLE9I> 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是一样的，也可以通过索引来获取所有符合条件的数据。

再看下一个例子：

suk@ORACLE9I> alter table t modify(a not null);

表已更改。

suk@ORACLE9I> 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

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

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