Oracle函数索引

创建一张表，只有很少量的记录状态为'N',而我们只需要查询状态为'N'的记录

SQL> create table test as select 'Y' flag,o.* from dba_objects o;
SQL> update test set flag='N' where rownum<10;

已更新9行。

SQL> commit;

提交完成。

然后分别创建函数索引和普通索引
SQL> create index inx_flag_n on test(decode(flag,'N','N',null));

索引已创建。

SQL> create index inx_flag on test(flag);

索引已创建。

SQL> exec dbms_stats.gather_schema_stats('LIHUILIN');

PL/SQL 过程已成功完成。

使用函数索引查询状态为"N"的记录，其结果如下
SQL> select flag,object_name from test where decode(flag,'N','N',null)='N';
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 905922165
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     9 |   261 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     9 |   261 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INX_FLAG_N |     9 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(DECODE("FLAG",'N','N',NULL)='N')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        627  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

如果使用普通索引，其结果如下。
SQL> select flag,object_name from test where flag='N';
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 36274 |   956K|   296   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST | 36274 |   956K|   296   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FLAG"='N')
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1059  consistent gets
          0  physical reads
          0  redo size
        627  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

由此可见，在这种场景下，普通索引甚至无法发生作用，而函数索引，则可以优化查询。