一、环境信息
-- 表信息
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也是有顺序的,就能用。