当前位置:操作系统 > Unix/Linux >>

[Index]Null值和索引的问题


[Index]Null值和索引的问题
 
Index是不存储NULL值的,所以如果你有NULL的条件在SQL中,那么相关的索引是用不到的。
CREATE TABLE PTIAN1 AS (SELECT * FROM DBA_OBJECTS);    www.zzzyk.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;  

 
解决方法
创建Function-Based Index 
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;  



 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,