oracle null 不走索引,搜索条件设置为Is Null一定不走索引吗？

声明：本文思路来源于laoxiong老师的博文，就此感谢！

在Oralce的搜索中，如果where条件句后使用了is null搜索条件，那么即使该列上使用了索引，Oracle也不会进行索引搜索。那么，这条规则一定正确吗？

构建实验环境

准备数据环境，构建数据表。

SQL> create table t as select object_id,object_name,owner from dba_objects;

Table created

//设置一些object_id为null的情况

SQL> update t set object_id=null where wner='PERFSTAT';

139 rows updated

SQL> commit;

Commit complete

SQL> select count(*) from t;

COUNT(*)

----------

52806

//构建单值索引

SQL> create index idx_t_id on t(object_id);

Index created

//收集统计信息

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all indexed columns');

PL/SQL procedure successfully completed

SQL> select * from dba_segments where segment_name='IDX_T_ID';

//索引：空间120个数据块，0.98MB。

SEGMENT_NAMESEGMENT_TYPEHEADER_BLOCKBYTESBLOCKSEXTENTS

--------------------------- ------------ ---------- ---------- ----------

IDX_T_IDINDEX6605798304012015

在这种情况下，使用索引列的is null条件，查询是不会走索引的。

SQL> select * from t where object_id is null;

已选择139行。

已用时间: 00: 00: 00.06

执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id| Operation| Name | Rows| Bytes | Cost (%CPU)| Time|

--------------------------------------------------------------------------

|0 | SELECT STATEMENT||139 |4726 |68(3)| 00:00:01 |

|*1 |TABLE ACCESS FULL| T|139 |4726 |68(3)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID" IS NULL)

统计信息

----------------------------------------------------------

128recursive calls

0db block gets

323consistent gets

0physical reads

0redo size

5416bytes sent via SQL*Net to client

484bytes received via SQL*Net from client

11SQL*Net roundtrips to/from client

3sorts (memory)

0sorts (disk)

139rows processed

实验说明，在有索引的情况下，使用is null的搜索语句是不会走索引路径的。

分析原因

这种情况出现的原因，本质上是因为oracle索引在生成过程中，是不会为索引列为null的数据行建索引，使之成为索引树的叶节点。更深层的原因，笔者认为是和null在oracle中，进行比较操作的过程中，表达式取值永远为null有关。

那么，对那些索引列为空的数据行，索引树上是没有对应的叶节点记录的，也就无从谈起借助索引进行搜索。

有一种思路，认为如果是构建组合索引，也就是将两个或两个以上列作为索引列，进行单列is null检索的时候，是可能会走索引的。原理很简单，对数据行来说，只有所有的索引列都是空的数据行，才不会被加入到索引叶节点。只要有一个索引列有值(或者定义其不能为空)，就可以通过索引获取到。

在laoxiong老师的博客中，学到了一种思路，既然索引列集合需要有非空值才能进入索引叶节点，那么可以利用常量进行索引构建。

常量组合索引

构建组合索引。

SQL> drop index idx_t_id;

Index dropped

SQL> create index idx_t_cmp1 on t(object_id,0); //找一个常量值0，构建索引；

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all indexed columns');

PL/SQL procedure successfully completed

此时，索引数据字典。

SQL> select * from dba_segments where segment_name='IDX_T_CMP1';

//发现组合索引的体积扩大很多，达到了256个块，2MB大小。

SEGMENT_NAMESEGMENT_TYPEHEADER_BLOCKBYTESBLOCKSEXTENTS

------------- ------------------ ------------ ---------- ---------- ----------

IDX_T_CMP1INDEX66057209715225617

进行搜索is null。

SQL> select * from t where object_id is null;

已选择139行。

已用时间: 00: 00: 00.03

执行计划

----------------------------------------------------------

Plan hash value: 3648234756

------------------------------------------------------------------------------------------

| Id| Operation| Name| Rows| Bytes | Cost (%CPU)|Time|

------------------------------------------------------------------------------------------

|0 | SELECT STATEMENT||139 |4726 |3(0)|00:00:01 |

|1 |TABLE ACCESS BY INDEX ROWID| T|139 |4726 |3(0)|00:00:01 |

|*2 |INDEX RANGE SCAN| IDX_T_CMP1 |139 ||2(0)|00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID" IS NULL)

统计信息

----------------------------------------------------------

139recursive calls

0db block gets

49consistent gets

0physical reads

0redo size

5416bytes sent via SQL*Net to client

484bytes received via SQL*Net from client

11SQL*Net roundtrips to/from client

3sorts (memory)

0sorts (disk)

139rows processed

结论：虽然使用了is null，但是查询还是选择了索引作为执行路径。相比较全表扫描的方法。对比如下。

#

常量索引

全表扫面

执行时间

0.03S

0.06S

CPU消耗

3

68

consistent reads(逻辑读)

49

323

对比项目中，发现性能提升比较明显。

利弊分析

使用索引是我们优化性能的一个重要手段。is null因为其特殊性，是不能纳入到优化范畴的。借助本文中的常数组合索引，可以实现is null查询的索引，同时兼顾原有取值查询的索引路径。

但是，这种方法是存在一定折中的，就是索引体积的变化。从上面的例子看出，使用常量组合索引之后，索引的体积扩大了一倍。这种扩大主要是对常量值叶占位和空行信息加入上。

实际应用中，也要考虑全表中空值的数量比例关系，选择合适的情景进行设置。

此外，还要注意索引列的长度体积。如果是数据值类型比较小的列，效果较好。如果数据值都比较大，Oracle优化器在选择路径的时候，可能还是会选择全表扫描。