DB / Oracle / ACE 精选

Oracle AskTOM 索引策略问答

2026.09.06

Oracle AskTOM 索引策略问答

来源：AskTOM (asktom.oracle.com) 适用版本：Oracle Database 全版本 文档版本：v1.0 / 2026-07-22

1. 概述

索引策略是 AskTOM 高频话题，Tom Kyte 反复强调”索引有代价”[1]。

详细见：Oracle B-Tree 索引详解、Oracle 位图索引详解。

2. Tom Kyte 索引观

2.1 核心观点

- 索引不是越多越好
- 索引有代价（DML、空间）
- 按查询设计
- 监控使用率
- 删除未使用

2.2 设计原则

- 单列 vs 复合
- 选择性
- 查询模式
- 覆盖索引

3. 索引类型

3.1 B-Tree

- 默认
- OLTP
- 高选择性

3.2 Bitmap

- 低基数
- 数据仓库
- 不适合 OLTP

3.3 反向键

- 顺序插入热点
- RAC
- 不支持范围

3.4 函数索引

- 函数列
- 计算
- 灵活

3.5 IOT

- 索引组织表
- 主键查询
- 节省空间

4. 复合索引

4.1 列顺序

- 等值条件列在前
- 范围条件列在后
- 选择性高的在前

4.2 示例

-- 查询：WHERE deptno=10 AND salary>5000
CREATE INDEX idx_emp ON emp(deptno, salary);

4.3 覆盖索引

- 索引包含所有查询列
- 避免 TABLE ACCESS
- 性能提升

5. 索引监控

5.1 启用监控

ALTER INDEX emp_idx MONITORING USAGE;

5.2 查看

SELECT * FROM v$object_usage;
-- USED: YES/NO

5.3 停止

ALTER INDEX emp_idx NOMONITORING USAGE;

5.4 Tom 建议

- 监控一段时间（一周/一月）
- 删除未使用索引
- 减少 DML 代价

6. 选择性

6.1 计算

SELECT 
  table_name,
  column_name,
  num_distinct,
  num_rows,
  ROUND(num_distinct/num_rows*100, 2) AS selectivity
FROM user_tab_columns
JOIN user_tables USING(table_name);

6.2 标准

- 高选择性：> 10%
- 低选择性：< 1%
- 中：评估

6.3 选择

- 高：B-Tree
- 低：Bitmap（OLAP）
- 评估：复合

7. 聚簇因子

7.1 定义

- 索引行与表行的物理顺序一致性
- 越接近行数 → 越好
- 影响执行计划

7.2 查看

SELECT index_name, clustering_factor 
FROM user_indexes 
WHERE table_name='EMP';

7.3 优化

- 重建表（按索引列排序）
- IOT
- 评估

8. 函数索引

8.1 示例

CREATE INDEX idx_upper_ename ON emp(UPPER(ename));

-- 查询
SELECT * FROM emp WHERE UPPER(ename) = 'ALICE';

8.2 Tom 案例

- 大小写不敏感查询
- 计算列
- 优化

8.3 限制

- 函数必须确定性
- 统计信息
- 查询必须匹配

9. 不可见索引

9.1 11g+

CREATE INDEX idx_test ON emp(salary) INVISIBLE;

9.2 测试

- 评估索引影响
- 不影响现有
- 测试

9.3 启用

ALTER SESSION SET optimizer_use_invisible_indexes=true;

10. 索引重建

10.1 Tom 观点

- 不要定期重建
- 除非碎片严重
- 在线重建

10.2 评估

SELECT 
  index_name,
  del_lf_rows,
  lf_rows,
  ROUND(del_lf_rows/lf_rows*100, 2) AS pct_deleted
FROM index_stats;

10.3 重建

ALTER INDEX emp_idx REBUILD ONLINE;

11. 常见问题

11.1 索引不使用

- 统计信息
- 函数
- 隐式转换
- 检查

11.2 索引失效

- DDL 操作
- 重建
- 监控

11.3 性能下降

- 索引过多
- DML 慢
- 监控

12. 最佳实践

  1. 按查询设计：索引
  2. 监控使用：v$object_usage
  3. 删除未使用：定期
  4. 复合索引：列顺序
  5. 覆盖索引：优化
  6. 选择性：评估
  7. 不要过度：DML 代价
  8. 统计信息：收集
  9. 测试：性能
  10. 文档：策略

13. 参考资料

[1] AskTOM, “Indexing Strategy”, https://asktom.oracle.com [2] Tom Kyte, “Expert Oracle Database Architecture”, Chapter 11