IS NULL 是否能用到索引？

一、环境信息

-- 表信息 
CREATE TABLE T
(
  I_ID                          NUMBER(31) NOT NULL,
  I_NAME                        VARCHAR2(255),
  I_FULLNAME                    VARCHAR2(255) NOT NULL,
  STATUS                        VARCHAR2(8),
  ……
  ……
  ……
  ……
  ……
);
-- 索引信息
ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (I_ID) ;
CREATE INDEX T_IDX1 ON T (I_NAME);
CREATE INDEX T_IDX2 ON T (I_NAME,I_FULLNAME);
CREATE INDEX T_IDX3 ON T (I_NAME,STATUS);
CREATE INDEX T_IDX4 ON T (STATUS,I_NAME);

二、测试

SELECT /*+INDEX(T,PK_T)*/I_NAME FROM T WHERE I_NAME IS NULL ;-- OK

SELECT /*+INDEX(T,T_IDX1)*/I_NAME FROM T WHERE I_NAME IS NULL ; -- hint失效
SELECT /*+INDEX(T,T_IDX1)*/I_NAME FROM T WHERE I_NAME IS NOT NULL ; -- OK

SELECT /*+INDEX(T,T_IDX2)*/I_NAME FROM T WHERE I_NAME IS NULL ;-- OK
SELECT /*+INDEX(T,T_IDX3)*/I_NAME FROM T WHERE I_NAME IS NULL ; -- hint失效
SELECT /*+INDEX(T,T_IDX4)*/COUNT(1) FROM T WHERE STATUS='1' AND I_NAME IS NULL ;-- OK，且I_NAME字段也能用到索引

三、总结

1、如果至少有一个索引字段（含复合索引）不为空，那么整表数据都存，其他索引字段为NULL的也存
2、如果所有索引字段（含复合索引）都可以为空，那么不是整表数据都存，索引字段均为NULL的不存
3、所以IS NULL可以用到复合索引
4、但如果不是复合索引，且字段允许为空，那么字段IS NULL不能用到索引,IS NOT NULL 才能用到索引

总而言之，索引表，每一列（索引列1，索引列2，……）全都为null的不存，能存的索引列NULL也是有顺序的，就能用。