oracle sql中涉及is null时如何优化（索引创建和直方图）

4rfc

oracle sql中涉及is null时如何优化（索引创建和直方图）
适用场景
试想下面的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的情况，我们需要在此列上创建适合的复合索引，如果没有好的候选列，可以添加虚拟列创建复合索引，以达到语句执行计划走索引的目的。
当这一列的值只有少数几个唯一值，并且每个值涉及的行数多少严重倾斜时，建议收集直方图，以便达到正确走索引的目的。