记得以前就搞过这个问题,可是时间长了就又记得不清楚了,所以决定记录下来,以便以后查看,也和大家分享下。
1.创建表
create table b (id number,name varchar2(64));
insert into b values(1,'aa');
insert into b values(2,'bb');
insert into b values(3,'cc');
insert into b values(4,'dd');
insert into b(name) values('ee');
2.创建索引
create index index_b on b(id);
3.explain plan for select id from b where id =2
4.select * from table(dbms_xplan.display);
Plan hash value: 1790459597
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| INDEX_B | 1 | 3 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=2)
可以看到即使索引列中存在null值,查询也是可以用到索引的。
5.做个null值作为条件的查询
explain plan for select id from b where id is null
select * from table(dbms_xplan.display);
Plan hash value: 1911541843
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| B | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID" IS NULL)
大家可以看到,这个时候就没有走索引了,
为什么null值差不了索引呢,原因是索引键值不会存储null值。
--个人见解,如有不对,欢迎指正!!!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23614171/viewspace-1326855/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23614171/viewspace-1326855/