Oracle的SQL语句中使用了函数的列可以创建索引，使用函数返回值查询。我们可以使用一个函数或者表达式来创建一个基于函数的索引。基于函数的索引中，函数可以是Oracle内置函数，也可以是用户自定义函数或表达式。自定义的函数每一次执行必须返回一致的值。 基于函数的索引的优点：1、提前计算结果提升查询性能。数据库将提前计算涉及一列或多列的函数的值，并把该值存储在创建的基于函数的索引中。因此查询可以通过索引来查询预先计算的值，而不是在查询时运输函数或表达式的值，所以这个所以可以提升查询性能。2、基于函数的索引在多数情况下使优化器有更多可能使用更有效的索引范围扫描，而不使用代价高的全表扫描。3、如确认创建基于函数的索引可以大幅提升查询性能，应用程序逻辑和代码不需要修改，对应用是透明的。 下面我们看一个例子 1、执行一个查询语句 where条件后面指定upper(last_name)
SQL> explain plan for select first_name,last_name,phone_number
from employees
where UPPER(last_name) = UPPER('xiaofei');
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 30 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("LAST_NAME")='XIAOFEI')
13 rows selected.
2、检查该列上是否有索引
SQL> select index_name,column_name from user_ind_columns 2 where table_name='EMPLOYEES'; INDEX_NAME COLUMN_NAME -------------------- -------------------- EMP_EMAIL_UK EMAIL EMP_EMP_ID_PK EMPLOYEE_ID EMP_DEPARTMENT_IX DEPARTMENT_ID EMP_NAME_IX FIRST_NAME EMP_JOB_IX JOB_ID EMP_MANAGER_IX MANAGER_ID EMP_NAME_IX LAST_NAME 7 rows selected. SQL>
从以上执行计划可以看到，优化器没有使用LAST_NAME列上的索引，执行了全表扫描。如谓语在索引列上使用了函数，则优化器不会选择该列上的索引。 虽然LAST_NAME列上有索引，但是优化器没有选择，是因为上面SQL语句中是以UPPER(LAST_NAME)作为的查询条件，而不是以LAST_NAME为查询条件。 如果让查询使用索引，就需要在查询条件表达式上创建索引，即基于函数的索引。 3、在upper(last_name)列创建一个基于函数的索引
SQL> create index emp_up_name on employees (upper(last_name));
Index created.
SQL> explain plan for select first_name,last_name,phone_number
from employees
where UPPER(last_name) = UPPER('xiaofei');
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1395557483
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 44 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_UP_NAME | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("LAST_NAME")='XIAODFEI')
14 rows selected.
SQL>
创建基于函数索引后，我们再次执行查询语句，数据库将使用基于函数的索引进行查询，执行计划 INDEX RANGE SCAN 。 4、查看基于函数的索引
SQL> select index_name,column_expression from user_ind_expressions;
INDEX_NAME COLUMN_EXPRESSION
-------------------- --------------------------------------------------------------------------------
EMP_UP_NAME UPPER("LAST_NAME")
SQL>
summary：基于函数的索引可以是B树索引，唯一索引或位图索引。基于函数的索引和虚拟索引可以结合使用。有些场景及时创建基于函数的索引，优化器仍然会走全表扫描；基于函数的索引在早期部分版本会出现错误结果集。
-the end-
