情况3：
对于一个有索引的列，做以下查询：
select * from t where function(indexed_column)=value;
却发现没有使用indexed_colum上的索引。原因是这个列上使用了函数。如果是对indexed_column的值建立了索引，而不是对function(indexed_column)的值建索引。在此不能使用这个索引。如果愿意，可以另外对函数建立索引。
zx@ORCL>select * from t where mod(x,999)=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 34000 | 153 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 1000 | 34000 | 153 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(MOD("X",999)=1)
zx@ORCL>create index idx_t_f on t(mod(x,999));
Index created.
zx@ORCL>exec dbms_stats.gather_table_stats(USER,'T',cascade=>true);
PL/SQL procedure successfully completed.
zx@ORCL>select * from t where mod(x,999)=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 4125918735
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3800 | 102 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 3800 | 102 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX_T_F | 100 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(MOD("X",999)=1)