在Oracle数据库中，我们经常遇到这样的情况：明明在某个字段上建了索引，但查询条件里一旦对这个字段使用函数，比如WHERE UPPER(name) = 'TOM'，执行计划就变成全表扫描了。原因很简单，普通索引存放的是原始列值，而查询条件要匹配的是函数计算后的结果，两者对不上。函数索引（Function-Based Index，简称FBI）正是为了解决这个问题而存在的，它直接针对表达式结果建立索引，让带函数的查询条件也能走索引。
函数索引的创建语法与典型示例
函数索引的创建语法和普通索引几乎一样，区别在于索引列的位置写的是表达式而不是单纯的列名。基本形式如下：
-- 对大写转换后的姓名列建函数索引 CREATE INDEX idx_emp_upper_name ON emp(UPPER(ename)); -- 对字符串前几位建索引，常见于按前缀查询 CREATE INDEX idx_emp_sub ON emp(SUBSTR(ename, 1, 4)); -- 对算术表达式建索引 CREATE INDEX idx_sal_year ON emp(sal * 12); -- 组合表达式与普通列的复合函数索引 CREATE INDEX idx_dept_upper ON emp(UPPER(ename), deptno);</code>
创建函数索引有一个前提条件容易被忽略：用户必须拥有CREATE ANY INDEX权限，或者在自己拥有的表上直接创建。另外，函数索引底层依赖的是虚拟列机制，Oracle会在数据字典中为表达式生成一个隐藏的虚拟列，索引实际上就是建在这个虚拟列上的。理解这一点对后面分析函数索引的限制很有帮助。
还有一个环境参数必须开启：QUERY_REWRITE_ENABLED。在较早的Oracle版本（9i之前）中，如果这个参数没有设为TRUE，优化器不会考虑函数索引。从10g开始默认值已经是TRUE，但如果你维护的是老系统或者参数被人改过，查询突然不走函数索引时不妨先检查这里：
-- 检查参数设置 SHOW PARAMETER query_rewrite_enabled; -- 如果不是TRUE则修改 ALTER SESSION SET query_rewrite_enabled = TRUE;
函数索引失效或创建失败的常见限制
第一个限制也是最硬性的：函数索引中的表达式必须返回确定性结果。也就是说，同样的输入必须得到同样的输出。像SYS_DATE、SYSDATE、USER、SOUNDEX这类非确定性函数，以及自定义函数中没有用DETERMINISTIC关键字声明的，都不允许用来建函数索引。如果强行创建，Oracle会直接报ORA-01743或ORA-30553错误。自定义函数的写法示例如下：
-- 声明为DETERMINISTIC的自定义函数才能用于函数索引 CREATE OR REPLACE FUNCTION calc_bonus(p_sal NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN p_sal * 0.2; END; / -- 之后即可用于创建函数索引 CREATE INDEX idx_bonus ON emp(calc_bonus(sal));
第二个限制是查询表达式必须与索引定义严格匹配。你建的是UPPER(ename)的索引，查询时写UPPER(ename) = 'TOM'才能命中；如果你写了LOWER(ename)或者表达式外面又套了一层函数，索引就用不上了。这一点是实践中最常见的失效原因，尤其是多个开发人员对同一张表写不同风格的SQL时，很容易出现索引形同虚设的情况。
第三个坑是隐式类型转换。如果索引建在TO_CHAR(hiredate, 'YYYYMMDD')上，而查询条件里写的是TO_CHAR(hiredate, 'YYYYMMDD') = 20240101（数字字面量），Oracle会在表达式结果上再做一次TO_NUMBER转换，表达式已经不匹配了，函数索引随之失效。解决办法是保证查询条件的数据类型与函数索引返回类型一致，写成字符串比较= '20240101'即可。
此外还要注意：函数索引要求表分析过统计信息，否则基于成本的优化器（CBO）可能低估函数索引的选择性；表达式所在列如果允许为NULL，NULL值的处理规则也和普通索引一样，全NULL的表达式结果不会进入B树索引。
如何验证函数索引是否生效
创建之后不要想当然地认为索引一定会被使用，验证是必要的。最直接的方式是查看执行计划：
EXPLAIN PLAN FOR SELECT * FROM emp WHERE UPPER(ename) = 'TOM'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
如果执行计划中出现INDEX RANGE SCAN并且对象名称是你的函数索引名，说明索引已生效；如果显示TABLE ACCESS FULL，则要回头检查表达式是否匹配、类型是否一致、统计信息是否新鲜。必要时可以先执行DBMS_STATS.GATHER_TABLE_STATS收集统计信息再测试：
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', cascade => TRUE);
除了执行计划，还可以通过数据字典视图DBA_IND_EXPRESSIONS查看函数索引对应的表达式定义，确认索引到底建在什么表达式上：
SELECT index_name, column_expression FROM dba_ind_expressions WHERE table_name = 'EMP';
最后提醒一点：函数索引虽然好用，但会增加DML维护成本，每次INSERT或UPDATE都要额外计算表达式结果，写入频繁的表上要权衡查询收益与写入开销。实践中建议只在确实存在大量带函数条件的查询、且这些查询是性能瓶颈时才引入函数索引，并配合应用程序统一SQL写法规范，避免同一个表达式出现多种写法导致索引失效。掌握这些限制和验证方法，函数索引就能成为你SQL调优工具箱里的一把利器。
Oracle函数索引函数索引创建Oracle索引优化修改时间：2026-09-11 09:02:44