让IS NULL走起索引

由于业务的需要：查看某列值为NULL的记录有多少。这是一个十分简单的需求，同时也很容易实现。无非就是使用如下语句：

SELECT * FROM table1 WHERE xx IS NULL;

结果得出的结果只有几十条记录,但是执行的时间比我想象中的长了许多。一般这样的查询只是要秒秒钟的问题，但是却花了十几秒，而平时使用IS NOT NULL也没有那么慢啊。于是在有空的时候就查看原因。
以下是一个模拟（附带着我的思路）：

构造测试表

DROP TABLE t PURGE;
CREATE TABLE t(a NUMBER);
CREATE INDEX idx_t ON t(a);
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(1);
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'T');
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>USER, indname=>'IDX_T');

遇到这样的问题我首先是看一下执行语句的相关的执行计划

SET NULL UNKOWN --设置如果只为NULL就以UNKOWN显示
SET AUTOTRACE ON EXPLAIN
SELECT * FROM t WHERE a IS NULL;
         A
----------
UNKNOWN
UNKNOWN

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |     6 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |     6 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A" IS NULL)

SELECT * FROM t WHERE a IS NOT NULL;   
         A
----------
         1
         1
         1
         1
         1
         1
         1

7 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1512349780

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     7 |    21 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_T |     7 |    21 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A" IS NOT NULL) 

从上的现象看出使用IS NULL时候没有使用索引，而使用IS NOT NULL有使用索引，这让我很郁闷。于是我顺道的就查看了一下IDX_T索引的所在的列。

如下：

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME 
FROM USER_IND_COLUMNS 
WHERE INDEX_NAME = 'IDX_T';

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------
T                              IDX_T                          A

很明显的展示了在表T的a列上是有索引的,这时我就在IS NULL和IS NOT NULL中迷茫了，不知所措啊。于是只能将问题放在一边了。直到一天我知道了DBMS_STATS.GATHER_INDEX_STATS()的作用的时候我又回想起了这个情况于是我有查看了一下有关索引的统计信息。

如下：

SELECT index_name, num_rows from user_indexes where index_name = 'IDX_T';
INDEX_NAME                       NUM_ROWS
------------------------------ ----------
IDX_T                                   7

从上面可以看出索引的统计信息的NUM_ROWS字段只有7行，而实际的数据总量是9行。按道理应该索引的NUM_ROWS应该是9才对缺少了2，很明显少了值为NULL的那2行。原来索引是不将NULL值的行记录在索引中的。在网上查了一下视乎有这种说法（本人不太确定只是猜测，毕竟统计信息的知识太多个人感觉好难，也容易混淆，难记）。
知道了情况就问呗，问如何解决这样的情况，最后得到了答案。创建一个伪列的索引就行了。
具体如下：

SET AUTOTRACE OFF
DROP INDEX idx_t;
CREATE INDEX idx_t ON t(a, 0);
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'T');
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>USER, indname=>'IDX_T');
SET AUTOTRACE ON EXPLAIN
SELECT * FROM t WHERE a IS NULL;
         A
----------
UNKNOWN
UNKNOWN


Execution Plan
----------------------------------------------------------
Plan hash value: 2296882198

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     2 |     6 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T |     2 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A" IS NULL)


SELECT * FROM t WHERE a IS NOT NULL; 
         A
----------
         1
         1
         1
         1
         1
         1
         1

7 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1512349780

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     7 |    21 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_T |     7 |    21 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A" IS NOT NULL)

从上面可以上到IS NULL 走索引了，IS NOT NULL 也走索引

SELECT index_name, num_rows from user_indexes where index_name = 'IDX_T';

INDEX_NAME                       NUM_ROWS
------------------------------ ----------
IDX_T                                   9

索引的统计信息是9和表的行数是一样的。

在接触到函数索引的时候也有提到以上类似的场景（查询NULL值的记录，NULL值的记录在表中不到10%）。就能使用函数索引来加快搜索。

如下：

SET AUTOTRACE OFF
DROP INDEX idx_t;
CREATE IDNEX idx_t ON t(DECODE(a, NULL,0, NULL));
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'T');
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>USER, indname=>'IDX_T');
SET AUTOTRACE ON EXPLAIN

SELECT * FROM t WHERE DECODE(a, NULL, 0, NULL) = 0;
         A
----------
UNKNOWN
UNKNOWN

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(DECODE(TO_CHAR("A"),NULL,0,NULL)=0)

SELECT index_name, num_rows from user_indexes where index_name = 'IDX_T';

INDEX_NAME                       NUM_ROWS
------------------------------ ----------
IDX_T                                   2

可以看到以上结果走了索引，而且统计信息只有2条。在这里我做一下解释(是个人理解的，不对请指正)我们在创建索引的时候是使用DECODE(a, NULL, 0, NULL)。这个函数很好理解就是使用a的值和NULL比较，如果相等就变成0，而如果不相等就变成NULL。在表的记录中a的值只有2行为NULL，7行是有真实值，所以在创建索引时就生成2个0值，和7个NULL值。而根据前面所的oracle不会将NULL值行加入索引中，所以只能将为0的2行加入索引中了。所以索引统计信息的NUM_ROWS为2。

注意：要让oracle使用函数索引在谓词(WHERE)后要有相关函数。如上的SELECT查询。