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