适用场景
试想下面的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);
查询执行计划变为了走索引：
|
SQL> set autotrace trace SQL> SQL> SQL> select-- /*+ INDEX(T ID_BD_INVMANDOC_bak_negallowed) */ 2 * 3 from BD_INVMANDOC_bak t where 4 t.negallowed is null or 5 t.negallowed ='N' 6 ;
61399 rows selected.
Execution Plan ----------------------------------------------------------
-------------------------------------------------------------------------------- ---------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------- ---------------
| 0 | SELECT STATEMENT | | 58559 | 9492K| 1774 |
| 1 | CONCATENATION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| BD_INVMANDOC_BAK | 49153 | 7968K| 1475 |
| 3 | INDEX RANGE SCAN | ID_BD_INVMANDOC_BAK_NEGALLOWED | 49153 | | 113 |
| 4 | TABLE ACCESS BY INDEX ROWID| BD_INVMANDOC_BAK | 9406 | 1524K| 299 |
| 5 | INDEX RANGE SCAN | ID_BD_INVMANDOC_BAK_NEGALLOWED | 9867 | | 25 |
-------------------------------------------------------------------------------- --------------- |
分析：
当索引中包含了查询列存在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的情况，我们需要在此列上创建适合的复合索引，如果没有好的候选列，可以添加虚拟列创建复合索引，以达到语句执行计划走索引的目的。
当这一列的值只有少数几个唯一值，并且每个值涉及的行数多少严重倾斜时，建议收集直方图，以便达到正确走索引的目的。