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 | | | | |
转载于:https://blog.51cto.com/rexyuan/411561