Oracle 函数索引详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

函数索引（Function-Based Index）在函数表达式上建索引[1]：

2. 创建

2.1 基本

CREATE INDEX idx_upper_name ON employees (UPPER(name));
CREATE INDEX idx_sal_year ON sales (EXTRACT(YEAR FROM sale_date));
CREATE INDEX idx_full_name ON employees (first_name || ' ' || last_name);

2.2 复合

CREATE INDEX idx_complex ON employees (UPPER(name), dept_id);

2.3 条件

CREATE INDEX idx_active ON employees (CASE WHEN status = 'ACTIVE' THEN id END);

3. 使用

3.1 查询匹配

-- 必须 EXACT 匹配
SELECT * FROM employees WHERE UPPER(name) = 'ALICE';
-- 使用索引

SELECT * FROM employees WHERE name = 'Alice';
-- 不使用函数索引

3.2 案例

-- 大小写不敏感
SELECT * FROM employees WHERE UPPER(name) = 'ALICE';

-- 计算
SELECT * FROM sales WHERE EXTRACT(YEAR FROM sale_date) = 2025;

4. 约束

4.1 函数确定性

-- 函数必须 DETERMINISTIC
CREATE OR REPLACE FUNCTION my_func(p VARCHAR2) RETURN VARCHAR2
  DETERMINISTIC IS
BEGIN
  RETURN UPPER(p);
END;
/

4.2 查询改写

ALTER SYSTEM SET query_rewrite_enabled = TRUE;
ALTER SYSTEM SET query_rewrite_integrity = trusted;

5. 统计

5.1 收集

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  'SCOTT', 'EMP',
  method_opt => 'FOR ALL HIDDEN COLUMNS SIZE 254'
);

5.2 扩展统计

-- 扩展统计匹配
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SCOTT', 'EMP', '(UPPER(name))') 
FROM dual;

详细见：Oracle 扩展统计详解。

6. 应用场景

6.1 大小写

-- 大小写不敏感查询
CREATE INDEX idx_upper_email ON users (UPPER(email));
SELECT * FROM users WHERE UPPER(email) = '[email protected]';

6.2 日期

-- 按年查询
CREATE INDEX idx_year ON sales (EXTRACT(YEAR FROM sale_date));
SELECT * FROM sales WHERE EXTRACT(YEAR FROM sale_date) = 2025;

6.3 计算

-- 计算列
CREATE INDEX idx_total ON orders (qty * price);
SELECT * FROM orders WHERE qty * price > 1000;

6.4 条件

-- 部分索引
CREATE INDEX idx_active ON emp (id) WHERE status = 'ACTIVE';
-- 12c+ 部分

7. 优势

7.1 优化

- 函数查询使用索引
- 避免 FULL TABLE SCAN
- 性能提升

7.2 灵活

- 复杂表达式
- 业务逻辑
- 索引

8. 限制

8.1 DETERMINISTIC

- 函数必须确定性
- 相同输入相同输出

8.2 匹配

- 查询必须 EXACT 匹配
- 函数 + 参数

8.3 维护

- DML 开销
- 索引维护
- 监控

9. 查看

9.1 视图

SELECT index_name, index_type, funcidx_status
FROM user_indexes
WHERE index_type LIKE 'FUNCTION-BASED%';

SELECT * FROM user_ind_expressions
WHERE table_name = 'EMP';

9.2 列

SELECT column_name, data_type
FROM user_tab_cols
WHERE table_name = 'EMP'
  AND hidden_column = 'YES';

10. 性能

10.1 优势

- 函数查询加速
- 减少全表扫描

10.2 开销

- DML 时计算
- 索引维护
- 监控

11. 监控

11.1 使用

ALTER INDEX idx_upper_name MONITORING USAGE;
SELECT * FROM v$object_usage;

11.2 性能

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

12. 常见问题

12.1 不使用

- 函数不匹配
- DETERMINISTIC
- query_rewrite

12.2 ORA-30553

- 函数非确定性
- DETERMINISTIC

12.3 性能

- DML 开销
- 监控
- 评估

13. 最佳实践

DETERMINISTIC：必须
查询改写：启用
统计：HIDDEN 列
匹配：查询
测试：使用
监控：使用
DML 评估：开销
复合：组合
文档：设计
演练：定期

14. 参考资料

[1] Oracle Database SQL Tuning Guide 19c, “Function-Based Indexes” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/