搜索条件设置为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_NAME    SEGMENT_TYPE  HEADER_BLOCK      BYTES     BLOCKS    EXTENTS

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

IDX_T_ID            INDEX                66057     983040        120         15 

 

在这种情况下，使用索引列的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)

统计信息

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

        128  recursive calls

          0  db block gets

        323  consistent gets

          0  physical reads

          0  redo size

       5416  bytes sent via SQL*Net to client

        484  bytes received via SQL*Net from client

         11  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

        139  rows 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_NAME  SEGMENT_TYPE    HEADER_BLOCK      BYTES     BLOCKS    EXTENTS

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

IDX_T_CMP1    INDEX                         66057    2097152        256         17 

 

 

 

进行搜索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)

 

统计信息

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

        139  recursive calls

          0  db block gets

         49  consistent gets

          0  physical reads

          0  redo size

       5416  bytes sent via SQL*Net to client

        484  bytes received via SQL*Net from client

         11  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

        139  rows processed

 

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

 

#	常量索引	全表扫面
执行时间	0.03S	0.06S
CPU消耗	3	68
consistent reads(逻辑读)	49	323

 

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

 

 

利弊分析

 

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

 

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

 

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

 

 

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

 

 

 

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

转载于:http://blog.itpub.net/17203031/viewspace-682427/