[Index]Null值和索引的问题
Index是不存储NULL值的，所以如果你有NULL的条件在SQL中，那么相关的索引是用不到的。
CREATE TABLE PTIAN1 AS (SELECT * FROM DBA_OBJECTS); www.2cto.com
CREATE INDEX PTIAN_INDEX ON PTIAN1(DATA_OBJECT_ID);
EXEC DBMS_STATS.GATHER_TABLE_STATS('APPS','PTIAN1', CASCADE=>TRUE );
SELECT * FROM PTIAN1 WHERE DATA_OBJECT_ID = 11;
SELECT * FROM PTIAN1 WHERE DATA_OBJECT_ID IS NULL;
解决方法
CREATE INDEX PTIAN_FUNC_INDEX ON PTIAN1 (NVL(DATA_OBJECT_ID,-1));
EXEC DBMS_STATS.GATHER_TABLE_STATS('APPS','PTIAN1', CASCADE=>TRUE );
SELECT OBJECT_NAME FROM PTIAN1 WHERE NVL(DATA_OBJECT_ID,-1) = -1;
