概述

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

SELECT * FROM table 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=>'SCOTT', tabname=>'T'); 
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>'SCOTT', indname=>'IDX_T');

对比执行计划

SET NULL UNKOWN --设置如果只为NULL就以UNKOWN显示 
SET AUTOTRACE ON EXPLAIN 
SELECT * FROM t WHERE a IS NULL; 
SELECT * FROM t WHERE 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';

很明显的展示了在表T的a列上是有索引的

查看索引统计信息

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

从上面可以看出索引的统计信息的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=>'SCOTT', tabname=>'T'); 
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>'SCOTT', indname=>'IDX_T'); 
SET AUTOTRACE ON EXPLAIN 
SELECT * FROM t WHERE a IS NULL; 
SELECT * FROM t WHERE a IS NOT NULL;

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

查看索引统计信息

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

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

篇幅有限，就写到这里了，这里大家可以抽空自己用函数索引来测试下，看到的num_rows也会不一样的，后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~