oracle如何让null值使用索引

时间：2010-10-27 来源：Rexyuan

SQL> create table ttt(id number,name varchar2(22)); Table created SQL> insert into ttt values(101,null); 1 row inserted SQL> commit; Table analyzed SQL> SQL> begin 2 for i in 1..100 3 loop 4 insert into ttt values(i,'ouleiq'||i); 5 commit; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed 有三种方式 1. 使用函数索引. SQL> create index idx_name on ttt(nvl(name,0)); 索引已创建。 SQL> analyze table ttt compute statistics for all indexes for all indexed columns; 表已分析。 SQL> select * from ttt where nvl(name,0)='0'; 执行计划 ---------------------------------------------------------- Plan hash value: 4255994954 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TTT | 1 | 21 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_NAME | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- 2. 替换null值. SQL> create index idx_ttt on ttt(name,'0'); Index created SQL> analyze table ttt compute statistics for all indexes for all indexed columns; SQL> select * from ttt where name is null; SQL> select * from ttt where name is null; 执行计划 ---------------------------------------------------------- Plan hash value: 2391132391 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 0 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TTT | 1 | 21 | 0 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TTT | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NAME" IS NULL) Note ----- - dynamic sampling used for this statement 3. 位图索引 SQL> create bitmap index idx_ttt_name_bit on ttt(name); 索引已创建。 SQL> analyze table ttt compute statistics for all indexes for all indexed columns; 表已分析。 SQL> select * from ttt where name is null; SQL> select * from ttt where name is null; 执行计划 ---------------------------------------------------------- Plan hash value: 2141313297 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TTT | 1 | 21 | 1 (0)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 3 | BITMAP INDEX SINGLE VALUE | IDX_TTT_NAME_BIT | | | | |