适用场景

试想下面的sql

Select*

fromBD_INVMANDOC_bak t

where t.negallowed is null

or t.negallowed='N'

如果negallowed列只有两个值：Y和N，并且存在NULL

那么我们在negallowed列创建一个索引，这个语句仍然不能走索引，原因为索引中不存储null值。

BD_INVMANDOC_bak表106万数据，其中5万行值为N，100万行为Y，其余为null。

如何走索引

需要创建一个包含虚拟列的复合索引，这样可以实现索引中包含negallowed列的null值

创建索引如下：

CREATEINDEX ID_BD_INVMANDOC_bak_negallowed ON BD_INVMANDOC_bak (negallowed,1);

查询执行计划变为了走索引：

分析：

当索引中包含了查询列存在null的时候，语句可以顺利走索引，语句执行代价也明显降低了。

但是通过进一步测试，发现在negallowed ='N'或negallowed ='Y'时均走索引，执行计划在negallowed ='Y'时非最优，所以需要采取直方图信息，以便执行计划更准确。

直方图histogram的影响

这个表的negallowed列只包含Y和N，并且存在null，那么直方图是不是对这列存在影响呢

使用如下语句收集，没有直方图

execdbms_stats.gather_table_stats(user,'BD_INVMANDOC_BAK',method_opt => 'FOR ALLINDEXED COLUMNS SIZE 1');

执行计划：

Execution Plan

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

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

| Id | Operation | Name | Rows | Bytes | Cost |

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

| 0| SELECT STATEMENT | | 530K| 84M| 7750 |

| 1| TABLE ACCESS FULL| BD_INVMANDOC_BAK| 530K| 84M| 7750 |

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

可以看到执行计划为全表扫描

统计直方图

execdbms_stats.gather_table_stats(user,'BD_INVMANDOC_BAK',method_opt => 'FOR ALLINDEXED COLUMNS SIZE auto');

执行计划：

Execution Plan

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

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

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

| Id | Operation |Name |Rows |

Bytes | Cost |

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

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

| 0| SELECT STATEMENT | | 64950 |

10M| 1969 |

| 1| CONCATENATION | | |

| |

| 2| TABLE ACCESS BY INDEX ROWID|BD_INVMANDOC_BAK | 55605 |

9014K| 1670 |

| 3| INDEX RANGE SCAN | ID_BD_INVMANDOC_BAK_NEGALLOWED |55605 |

| 128 |

| 4| TABLE ACCESS BY INDEX ROWID|BD_INVMANDOC_BAK | 9345 |

1514K| 299 |

| 5| INDEX RANGE SCAN | ID_BD_INVMANDOC_BAK_NEGALLOWED| 9867 |

| 25 |

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

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

--执行计划走索引

总结

当一个查询中涉及IS NULL的情况，我们需要在此列上创建适合的复合索引，如果没有好的候选列，可以添加虚拟列创建复合索引，以达到语句执行计划走索引的目的。

当这一列的值只有少数几个唯一值，并且每个值涉及的行数多少严重倾斜时，建议收集直方图，以便达到正确走索引的目的。