单字段索引中,字段的NULL值是不存放与INDEX中的,因此WHERE中的IS NULL条件一般情况下无法使用INDEX来优化,那么,当需要从一个海量数据表中查找某个字段IS NULL的少量记录时如何进行优化呢?
1) 利用复合索引,如果索引中至少有一个字段是非空字段,则即使其他字段是NULL值,整条记录仍然会被索引;
2) 如果没有合适的非空字段可以建立复合索引时,利用固定值复合索引。
下面给出一个例子:
建一个测试表test10,此时object_id和ojbect_name字段都是可以为空的:
create table test10 as select object_id,object_name,object_type from dba_objects;
为object_name字段建立索引:
create index idx_test10_1 on test10(object_name);
检查SQL的执行计划:
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 5 |
|* 1 | TABLE ACCESS FULL | TEST10 | 1 | 29 | 5 |
--------------------------------------------------------------------
将object_id改成非空,再建立复合索引:
alter table test10 modify object_id not null;
create index idx_test10_2 on test10(object_name,object_id);
检查SQL的执行计划:
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST10 | 1 | 29 | 1 |
|* 2 | INDEX RANGE SCAN | IDX_TEST10_2 | 1 | | 1 |
-----------------------------------------------------------------------------
删掉复合索引,建立一个固定值复合索引:
drop index idx_test10_2;
create index idx_test10_2 on test10(object_name,1);
检查SQL的执行计划:
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST10 | 1 | 31 | 1 |
|* 2 | INDEX RANGE SCAN | IDX_TEST10_2 | 1 | | 1 |
1) 利用复合索引,如果索引中至少有一个字段是非空字段,则即使其他字段是NULL值,整条记录仍然会被索引;
2) 如果没有合适的非空字段可以建立复合索引时,利用固定值复合索引。
下面给出一个例子:
建一个测试表test10,此时object_id和ojbect_name字段都是可以为空的:
create table test10 as select object_id,object_name,object_type from dba_objects;
为object_name字段建立索引:
create index idx_test10_1 on test10(object_name);
检查SQL的执行计划:
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 5 |
|* 1 | TABLE ACCESS FULL | TEST10 | 1 | 29 | 5 |
--------------------------------------------------------------------
将object_id改成非空,再建立复合索引:
alter table test10 modify object_id not null;
create index idx_test10_2 on test10(object_name,object_id);
检查SQL的执行计划:
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST10 | 1 | 29 | 1 |
|* 2 | INDEX RANGE SCAN | IDX_TEST10_2 | 1 | | 1 |
-----------------------------------------------------------------------------
删掉复合索引,建立一个固定值复合索引:
drop index idx_test10_2;
create index idx_test10_2 on test10(object_name,1);
检查SQL的执行计划:
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST10 | 1 | 31 | 1 |
|* 2 | INDEX RANGE SCAN | IDX_TEST10_2 | 1 | | 1 |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4555/viewspace-711107/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4555/viewspace-711107/