Oracle 自动索引详解

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

1. 概述

自动索引（Auto Index）是 19c 自动化索引管理[1]：

2. 特性

2.1 自动

- 自动创建
- 自动验证
- 自动监控
- 自动删除

2.2 流程

1. 捕获候选 SQL
2. 创建不可见索引
3. 验证性能
4. 可见或删除
5. 监控使用
6. 删除未用

3. 启用

3.1 配置

EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_MODE', 'IMPLEMENT'
);

-- 仅报告
EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_MODE', 'REPORT ONLY'
);

3.2 表空间

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

3.3 限制

-- Schema
EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_SCHEMA', 'SCOTT', FALSE  -- 排除
);

-- 恢复
EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_SCHEMA', NULL, TRUE
);

4. 查看

4.1 配置

SELECT parameter_name, parameter_value
FROM dba_auto_index_config;

4.2 索引

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

4.3 详情

SELECT * FROM dba_auto_index_ind_actions;
SELECT * FROM dba_auto_index_sql_actions;

5. 报告

5.1 生成

SELECT DBMS_AUTO_INDEX.REPORT_LAST_RUN()
FROM dual;

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

5.2 类型

- TEXT
- HTML
- XML

5.3 内容

- 摘要
- 候选索引
- 验证结果
- 创建/删除
- 错误

6. 流程

6.1 捕获

- 监控 SQL
- 识别候选
- 高频
- 性能

6.2 创建

- 创建不可见索引
- 不影响现有 SQL
- 测试

6.3 验证

- SQL 使用新索引
- 性能改善
- 验证

6.4 可见

- 验证通过
- 索引可见
- 生效

6.5 监控

- 使用情况
- 未用删除
- 维护

7. 控制

7.1 暂停

EXEC DBMS_AUTO_INDEX.CONFIGURE(
  'AUTO_INDEX_MODE', 'OFF'
);

7.2 删除

-- 删除自动索引
EXEC DBMS_AUTO_INDEX.DELETE_AUTO_INDEXES(
  index_name => 'SYS_AI_xxx'
);

7.3 手动

- 自动创建的索引
- 可手动管理
- 谨慎

8. 应用场景

8.1 OLTP

- 频繁查询
- 自动识别
- 优化

8.2 仓库

- 复杂查询
- 自动建议
- 评估

8.3 维护

- 自动清理
- 未用删除
- 节省

9. 优势

9.1 自动化

- 无需人工
- 自动优化
- 7×24

9.2 安全

- 不可见测试
- 验证后可见
- 不影响

9.3 持续

- 持续监控
- 自动调整
- 优化

10. 限制

10.1 不支持

- 临时表
- 外部表
- 部分类型

10.2 资源

- CPU 开销
- 空间
- 监控

11. 监控

11.1 任务

SELECT task_name, status, last_good_date
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';

11.2 索引

SELECT index_name, table_name, visibility, status, auto
FROM dba_indexes
WHERE auto = 'YES'
ORDER BY created DESC;

12. 常见问题

12.1 不创建

- 配置
- SQL 未识别
- 检查报告

12.2 性能

- 资源消耗
- 监控
- 调整

12.3 删除有用

- 监控期
- 评估
- 标记

13. 最佳实践

IMPLEMENT：生产
REPORT ONLY：测试
表空间：专用
Schema：限制
监控：报告
测试：性能
资源：评估
文档：配置
演练：定期
复盘：总结

14. 参考资料

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