当索引遇到null

让"is null"的条件判断也能走上索引。该方法就是创建一个"伪复合索引"

首先，创建一些数据：

create table t02(id number,names varchar(100)) ; 

insert into t02 select rownum ,object_name from all_objects;  

update t02 set id=NULL where id=1234;

update t02 set id=NULL where id=2234;

update t02 set id=NULL where id=3234;

update t02 set id=NULL where id=4234;

查询如下：

select count(*),count(id) from t02; 

 COUNT(*) COUNT(ID)

17920	    17916

总共17920条记录，其中id is null的有4条。

往常在id列建一个普通的索引，可以预见对于非null的条件查询非常有帮助，而对于id is null 就无能为力了。

create index idx__t02_id on t02(id); 

SET AUTOTRACE ON ; 
select * from t02 where id=1234 ; 

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    65 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T02         |     1 |    65 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX__T02_ID |    69 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

SET AUTOTRACE ON ; 
select * from t02 where id is null ;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |   130 |    21   (0)| 00:00:01 |
|*  1 | TABLE ACCESS FULL| T02  |     2 |   130 |    21   (0)| 00:00:01 |
--------------------------------------------------------------------------

id is null 这个条件判断有将近 1/10000 的选择率，不走索引有时这让人非常郁闷。此时，再建一个“伪符合索引”。

CREATE INDEX cidx__t02_id ON t02(id,0);

SET AUTOTRACE ON ; 
select * from t02 where id is null ;

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     5 |   325 |     8   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T02          |     5 |   325 |     8   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | CIDX__T02_ID |   862 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 

id is null 也能走索引了。

在举个例子。

create table t03(id number,name varchar(200) ); 

insert into t03 select rownum,object_name from all_objects; 

update t03 set id=1 where id<50; 

update t03 set id=0 where id<>1;

查询如下：

select id,count(*) from t03 group by id;

 id  COUNT(*)

1     49
0     17876

我们所关心的是id = 1 的那一部分数据，如果建立一个普通的B-Tree索引就要保存17925个条目，但我们实际关心其中的49条。

此时，我们建立俩个索引，顺便手机统计信息。

create index fidx__t03_id_eq_1 on t03( decode(id,1,1) ) ;
exec dbms_stats.gather_table_stats( user , 't03' , cascade=>true );

索引fidx__t03_id_eq_1仅包含我们关心的49条记录：

select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys
from user_indexes 
where table_name='T03';

INDEX_NAME         INDEX_TYPE              BLEVEL LEAF_BLOCKS   NUM_ROWS STATUS   DISTINCT_KEYS

FIDX__T03_ID_EQ_1  FUNCTION-BASED NORMAL        0           1         49  VALID               1

set autotrace on ; 
select count(*) from t03 where decode(id,1,1)=1;

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |     2 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                   |     1 |     2 |            |          |
|*  2 |   INDEX RANGE SCAN| FIDX__T03_ID_EQ_1 |    49 |    98 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

参考地址

http://www.cnblogs.com/killkill/archive/2010/06/30/1768442.html