索引失效系列——说说is null

索引是我们进行优化的一种重要方式。实际工作中，一个简单的索引，可能就会大大提升提高关键业务作业效率，最终提升用户满意度。在CBO时代，DBA和开发人员经常为索引为什么不出现在执行计划中而困惑。

 

 

问题提出

 

我们在一些SQL优化的规则中（虽然已经进入了CBO时代），经常看到“where条件中出现is null，搜索不走索引”这样的说法。这样说法的原因是什么呢？我们通过实验一起来看一下。

 

 

SQL> create table t as select * from dba_objects;

 

Table created

 

SQL> update t set wner=null where wner='SCOTT';

 

34 rows updated

 

SQL> commit;

 

Commit complete

 

 

数据总量为：

 

 

SQL> select count(*) from t;

 

 COUNT(*)

----------

    51367

 

 

针对owner列为空的情况来说，超过五万的数据列中只有34行，选择性是相当好的，起码对于null来说。那么，我们加入索引。

 

SQL> create index idx_t_owner on t(owner);

 

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

 

下面我们实验对is null条件的检索路径。

 

 

SQL> explain plan for select * from t where owner is null;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|  0 | SELECT STATEMENT |     |   70 | 6510 |  158  (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T   |   70 | 6510 |  158  (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

  1 - filter("OWNER" IS NULL)

 

13 rows selected

 

SQL> rollback;

 

Rollback complete

 

 

现象和流传的规则相同，当我们条件中出现is null，也就是试图搜索列值为空的数据行，索引路径是不出现在执行计划中的。

 

如果不使用is null，转为对取值列进行搜索，索引路径是可行的。

 

 

SQL> explain plan for select * from t where wner='PERFSTAT';

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1516787156

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

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

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

|  0 | SELECT STATEMENT           |            |  136 | 12648 |    5  (0)|

|  1 | TABLE ACCESS BY INDEX ROWID| T      |  136 | 12648 |    5  (0)|

|* 2 |  INDEX RANGE SCAN    | IDX_T_OWNER |  136 |      |    1  (0)|

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

Predicate Information (identified by operation id):

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

  2 - access("OWNER"='PERFSTAT')

 

14 rows selected

 

 

对应is null条件，还存在一个is not null条件。如果选择率合适，is not null执行计划中时会带入索引路径吗？

 

 

SQL> explain plan for select * from t where owner is not null;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3329754318

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

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

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

|  0 | SELECT STATEMENT           |            | 51297 | 4658K|   49  (0)|

|  1 | TABLE ACCESS BY INDEX ROWID| T          | 51297 | 4658K|   49  (0)|

|* 2 |  INDEX FULL SCAN          | IDX_T_OWNER |  139 |      |    1  (0)|

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

Predicate Information (identified by operation id):

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

  2 - filter("OWNER" IS NOT NULL)

 

14 rows selected

 

 

 

一系列的实验，结论是：当我们在where语句中加入is null条件时，对应列索引是不会出现在执行计划中的。Is not null条件在选择率合适的情况下，还是可能引入索引执行计划的。

 

原因分析

 

 

从is null不走索引和is not null走索引这个现象，我们可以看出问题的一些端倪。Is null虽然不是通常的where条件格式，但是绝对不是引起执行计划问题的核心因素。

 

核心原因还是在于数据列中出现null值的特殊性。我们思考一下索引构建的过程，将索引列值进行建树，其中必然涉及到诸多的比较操作。Null值的特殊性就在于参与的运算大多取值为null。这样的话，null值实际上是不能参与进建索引的过程。也就是说，null值不会像其他取值一样出现在索引树的叶子节点上。

 

 

这种情况是比较容易被证明的。我们在一些null值较多的列上建索引，会发现形成的索引树体积很小，与列值不相匹配。

 

SQL> select count(*) from t where owner is not null;

 

 COUNT(*)

----------

      139

 

SQL> select num_rows from dba_indexes where index_name='IDX_T_OWNER';

 

 NUM_ROWS

----------

      139

 

 

从字典上看出，索引中对应的函数正好等于列非空的行数。证明空值的确是不会进入索引。这样is null不走索引就好理解了，因为对应的行根本就没有在索引树上，所以也就不存在执行计划中出现索引路径了。

 

 

如何选择

 

我们了解了is null的特性，那么我们如何进行列选择呢？如果我们希望is null的时候出现索引路径，应该怎么做呢？

 

答案是借助适当的默认值和详细的功能需求分析。在笔者之前的文章《非空+默认值——一种选择方案思路》（http://space.itpub.net/17203031/viewspace-692784）中已经对这个问题进行过比较详细的说明。

 

对null列的处理，我们可以通过列默认值+非空字段的方法保证列不为空。这样每行数据必然出现在索引树上，对原有null的检索条件就变为等于默认值的检索条件，是可能出现索引执行计划的。但是，要注意选择的原则：

 

ü       原有对is null检索的业务需求是否强烈，是否重要。如果通常没有对该列is null的需求，或者该列绝大多数情况都是为null（即使有索引也会因为成本原因不走），那么还是不建议设置默认值方案。因为小索引树结构还是有很多优势的；

ü       在对is null选择是关键业务用例需求，并且取值null的行比较少，选择率较好的时候，才考虑使用默认值方法来进行索引优化；

 

 

进入CBO时代之后，优化器变得越来越强大，同时也越来越复杂。只有通过不断的实验，结合实际需求和优化方向，才能在资源和性能之间选择出一个最优的平衡点。