oracle中的index函数,Oracle PL/SQL之函数索引（Function-based indexes）使用示例

函数索引(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://blog.csdn.net/t0nsha/article/details/6730976