Home >Database >Mysql Tutorial >[Index]Null值和索引的问题

[Index]Null值和索引的问题

WBOY
WBOYOriginal
2016-06-07 14:53:461280browse

[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


[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;  


[Index]Null值和索引的问题
 

SELECT * FROM PTIAN1 WHERE DATA_OBJECT_ID IS NULL;  


[Index]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;  


[Index]Null值和索引的问题

 

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn