is null 条件使用索引的方法

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

-------------------------------------------------------------------------------------------------