WHERE 条件中的Is null 不能使用索引，除非加上not null的约束

WHERE 条件中的Is null 不能使用索引，除非加上not null的约束 。

 

select * from test_jack

a

a

a

a

a

a

a

a

a

 

create index test_b on test_jack (b)

 

 

select * from test_jack where b='a'

SELECT STATEMENT, GOAL = FIRST_ROWS               Cost=1    Cardinality=1  Bytes=2009

 TABLE ACCESS BY INDEX ROWID Object wner=JAPAN    Object name=TEST_JACK    Cost=1    Cardinality=1  Bytes=2009

INDEX RANGE SCAN     Object wner=JAPAN    Object name=TEST_B   Cost=1    Cardinality=1 

 

select * from test_jack where b is null

SELECT STATEMENT, GOAL = FIRST_ROWS                  Cost=2    Cardinality=4  Bytes=8036

 TABLE ACCESS FULL    Object wner=JAPAN    Object name=TEST_JACK    Cost=2    Cardinality=4  Bytes=8036

 

select * from test_jack where b is not null

SELECT STATEMENT, GOAL = FIRST_ROWS                  Cost=66  Cardinality=4  Bytes=8036

 TABLE ACCESS BY INDEX ROWID    Object wner=JAPAN    Object name=TEST_JACK    Cost=66  Cardinality=4       Bytes=8036

  INDEX FULL SCAN     Object wner=JAPAN    Object name=TEST_B   Cost=26  Cardinality=4 

 

将null值删除

Delete from test_jack where b is null ;

 

select * from test_jack where b is null

SELECT STATEMENT, GOAL = FIRST_ROWS                  Cost=2    Cardinality=4  Bytes=8036

 TABLE ACCESS FULL    Object wner=JAPAN    Object name=TEST_JACK    Cost=2    Cardinality=4  Bytes=8036

 

分析表

Exec dbms_stats.gather_table_stats ('japan','test_jack')

还是没用到索引

 

select * from test_jack where b is null

SELECT STATEMENT, GOAL = FIRST_ROWS                  Cost=2    Cardinality=4  Bytes=8036

 TABLE ACCESS FULL    Object wner=JAPAN    Object name=TEST_JACK    Cost=2    Cardinality=4  Bytes=8036

 

将b字段设为非空

alter table test_jack modify b not null

 

select count(*) from test_jack t where b is  null

SELECT STATEMENT, GOAL = FIRST_ROWS                  Cost=1    Cardinality=1  Bytes=2

 SORT AGGREGATE                            Cardinality=1  Bytes=2

  INDEX RANGE SCAN  Object wner=JAPAN    Object name=TEST_B   Cost=1    Cardinality=1  Bytes=2

此时用到了索引

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30821/viewspace-606433/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30821/viewspace-606433/