Oracle 19c 自动索引

适用版本：Oracle Database 19c+ 文档版本：v1.0 / 2026-07

1. 概述

自动索引（Auto Indexing） 是 19c 引入特性[1]：

特点：

自动创建索引
自动验证
自动删除无用

2. 启用

2.1 配置

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');
-- IMPLEMENT：自动创建
-- REPORT：仅报告
-- OFF：禁用

2.2 表空间

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'INDEX_TBS');

2.3 模式

-- 启用 schema
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SCOTT', TRUE);

-- 禁用 schema
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', FALSE);

3. 工作流程

3.1 后台进程

每 15 分钟运行
分析 SQL
创建候选索引
验证
实施或删除

3.2 阶段

1. 候选识别
2. 创建隐藏索引
3. SQL 性能验证
4. 可见化或删除

4. 监控

4.1 配置查看

SELECT * FROM dba_auto_index_config;

4.2 索引详情

SELECT 
  index_name,
  table_name,
  table_owner,
  status,
  visibility,
  auto,
  created
FROM dba_indexes
WHERE auto = 'YES';

4.3 执行历史

SELECT 
  execution_name,
  execution_start,
  execution_end,
  status,
  error_code
FROM dba_auto_index_executions
ORDER BY execution_start DESC;

5. 报告

5.1 概要报告

SELECT DBMS_AUTO_INDEX.REPORT_LAST_REPORT() FROM dual;
SELECT DBMS_AUTO_INDEX.REPORT_LAST_REPORT('HTML') FROM dual;

5.2 详细报告

SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(
  activity_start => SYSTIMESTAMP - 1,
  activity_end => SYSTIMESTAMP,
  type => 'TEXT'
) FROM dual;

6. 索引管理

6.1 删除自动索引

EXEC DBMS_AUTO_INDEX.drop_auto_indexes('SCOTT', 'SYS_AI_...');

6.2 手动删除

-- 19c 之前自动索引不能手动 DROP
-- 19c+ 允许
DROP INDEX SYS_AI_...;

7. 验证

7.1 性能提升

-- 自动索引使用
SELECT 
  sql_id, 
  plan_hash_value,
  executions,
  elapsed_time,
  buffer_gets
FROM v$sql
WHERE sql_text LIKE '%...%'
ORDER BY elapsed_time;

7.2 索引使用监控

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

8. 适用场景

8.1 推荐

大量 SQL 工作负载
缺乏 DBA 优化
复杂应用
第三方应用

8.2 不推荐

OLTP 高并发
索引已优化
小表

9. 限制

需要企业版
仅 B-Tree
不支持函数索引
表空间专用

10. 配置参数

参数	说明
AUTO_INDEX_MODE	模式
AUTO_INDEX_DEFAULT_TABLESPACE	表空间
AUTO_INDEX_SCHEMA	Schema
AUTO_INDEX_REPORT_RETENTION	报告保留
AUTO_INDEX_RETENTION_FOR_AUTO	自动索引保留
AUTO_INDEX_RETENTION_FOR_MANUAL	手动索引保留

11. 常见坑与排错

11.1 索引未创建

-- 1. 检查模式
SELECT * FROM dba_auto_index_config;

-- 2. 检查执行
SELECT * FROM dba_auto_index_executions;

-- 3. 报告
SELECT DBMS_AUTO_INDEX.REPORT_LAST_REPORT() FROM dual;

11.2 性能变差

-- 1. 禁用自动索引
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'OFF');

-- 2. 删除问题索引
EXEC DBMS_AUTO_INDEX.drop_auto_indexes(...);

12. 最佳实践

测试环境验证：先测试
IMPLEMENT 模式：自动
专用表空间：管理
Schema 启用：控制
监控报告：效果
定期审查：质量
结合手工：综合
监控性能：影响
业务低峰：测试
文档化：记录

13. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “Auto Indexing” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/