Oracle 函数索引是什么？什么时候适合创建 Function-Based Index？

简化版

Oracle 函数索引是基于表达式或函数结果创建的索引，比如 upper(name) 或 trunc(created_at)。当查询条件经常对列做函数处理，普通索引无法有效使用时，可以考虑函数索引。但函数必须稳定，表达式要和查询一致，并注意 DML 维护成本。

详细版

普通索引建在列值上，如果 SQL 写：

where upper(user_name) = 'TOM'

普通 user_name 索引可能无法直接使用。函数索引可以建在 upper(user_name) 上，让这类查询走索引。

适用场景：

大小写不敏感查询。
按日期截断查询。
对表达式结果过滤。
需要部分模拟条件索引。

风险：

DML 时要维护表达式结果。
查询表达式要匹配。
函数应确定性强。
滥用会增加索引数量和维护成本。

完整版教学

一、函数作用在列上可能让普通索引失效

普通 B-tree 索引保存的是原始列值。如果查询条件对列做函数处理，数据库看到的是函数结果，不一定能用原始列索引。

where upper(user_name) = 'TOM'
where trunc(created_at) = date '2026-07-29'

这类写法在很多数据库里都会影响索引使用。Oracle 的函数索引就是为表达式查询提供索引能力。

记忆钩子：普通索引索原值，函数索引索表达式结果。

二、函数索引建在表达式上

函数索引不是只能建在函数上，也可以建在表达式上。核心是把查询里常用的表达式结果提前索引起来。

create index idx_users_upper_name
on users(upper(user_name));

查询时如果使用相同表达式：

select *
from users
where upper(user_name) = 'TOM';

优化器就有机会使用这个函数索引，而不是全表扫描。

三、日期截断查询是经典场景

很多人喜欢写：

where trunc(created_at) = date '2026-07-29'

如果没有函数索引，这可能导致普通 created_at 索引难以使用。更推荐的写法是范围查询：

where created_at >= date '2026-07-29'
  and created_at <  date '2026-07-30'

如果业务确实大量使用 trunc(created_at) 维度，也可以建函数索引。面试时最好同时说出“能改写范围查询时优先改写”。

四、函数索引可以支持大小写不敏感查询

用户名、邮箱、编码查询可能要求大小写不敏感。可以统一存小写，也可以用函数索引。

create index idx_users_lower_email
on users(lower(email));

查询时：

where lower(email) = lower(:email)

如果邮箱是登录账号，还要结合唯一性要求，考虑基于 lower(email) 的唯一函数索引，防止 Tom@x.com 和 tom@x.com 重复。

五、表达式匹配和统计信息很重要

函数索引能否使用，取决于查询表达式是否能和索引表达式匹配，以及优化器是否认为它划算。如果写法变化很大，可能无法命中。

索引: upper(user_name)
查询: upper(user_name) = :name -> 容易匹配
查询: user_name = :name -> 不使用该函数索引

创建函数索引后也要收集统计信息，让优化器知道它的选择性。

六、函数索引不是免费午餐

每次 insert、update 相关列时，Oracle 都要计算表达式并维护索引。索引越多，写入越慢，空间占用也越大。

收益	成本
特定表达式查询更快	DML 维护成本增加
避免全表扫描	占用额外空间
支持大小写不敏感唯一	表达式和函数要稳定

所以函数索引适合高频、稳定、选择性较好的表达式查询，不适合随手给所有函数条件建索引。

七、常见误区与追问

误区：函数索引能优化所有函数查询。 只有表达式匹配且优化器认为成本合适时才会使用。
误区：日期查询必须用 trunc。 半开时间范围通常更利于普通索引，也更通用。
误区：函数索引没有写入成本。 DML 时需要计算表达式并维护索引。
追问：大小写不敏感唯一怎么做？ 可以创建 unique index on lower(email)。
追问：函数必须 deterministic 吗？ 表达式应稳定可重复，非确定性函数不适合作索引基础。
追问：建了函数索引为什么没走？ 可能表达式不匹配、统计信息不准、选择性差或优化器认为全表扫描更便宜。

八、面试中可以这样落地

如果系统经常按邮箱大小写不敏感登录，可以设计：

create unique index uk_users_lower_email
on users(lower(email));

应用写入时也可以统一转小写，减少函数计算。查询性能问题出现时，要用执行计划验证函数索引是否命中。

九、加强记忆

函数索引记住“表达式结果也能建索引”。它适合 upper/lower/trunc 这类稳定高频表达式查询，但要优先考虑能否改写 SQL，特别是时间范围查询。面试时一定要补上 DML 成本和表达式匹配要求。

简化版

详细版