Oracle PL/SQL之函数索引（Function-based indexes）使用示例-优快云博客

本文通过具体示例展示了在Oracle数据库中如何利用函数索引来优化查询效率。特别是当WHERE子句中涉及到列上的函数操作时，创建相应的函数索引能够显著提升查询速度。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

函数索引（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

原文链接： http://blog.youkuaiyun.com/t0nsha/article/details/6730976

转载于:https://my.oschina.net/dtec/blog/46693