首相，建立一張表，只有很少量的記錄狀態為'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

由此可見，在這種場景下，普通索引甚至無法發生作用，而函式索引，則可以最佳化查詢。

oracle函式索引

相關文章