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 |       |       |            |          |