函数索引(Function-based indexes)只有在where条件使用了与索引中相同的基于相同列的函数时才起作用。
duzz$scott@orcl>set autotrace on
duzz$scott@orcl>create table t1 as select * from dept;
Table created.
Elapsed: 00:00:00.01
duzz$scott@orcl>create index loc_idx on t1(upper(loc));
Index created.
Elapsed: 00:00:00.06
duzz$scott@orcl>select * from t1 where deptno=20;
DEPTNO DNAME LOC
---------- ------------------------------------------ ------------
20 RESEARCH DALLAS
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=20)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
533 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
duzz$scott@orcl>select * from t1 where loc='DALLAS';
DEPTNO DNAME LOC
---------- ------------------------------------------ -----------------
20 RESEARCH DALLAS
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LOC"='DALLAS')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
533 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
duzz$scott@orcl>select * from t1 where upper(loc)='DALLAS';
DEPTNO DNAME LOC
---------- ------------------------------------------ ----------------------
20 RESEARCH DALLAS
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3763008475
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LOC_IDX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("LOC")='DALLAS')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
533 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
duzz$scott@orcl>
REF:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm