Oracle Jonathan Lewis 索引策略深度

来源：Jonathan Lewis / jonathanlewis.wordpress.com 适用版本：Oracle Database 8i+ 文档版本：v1.0 / 2026-07-22

1. 概述

Jonathan Lewis 对索引策略有深度分析[1]。

2. 索引基础

2.1 B-Tree 结构

- Root
- Branch
- Leaf
- 行数据（ROWID）

2.2 访问路径

- INDEX UNIQUE SCAN
- INDEX RANGE SCAN
- INDEX FAST FULL SCAN
- INDEX FULL SCAN
- INDEX SKIP SCAN

2.3 Jonathan 观点

- 索引是双刃剑
- 加速查询，减慢 DML
- 平衡

3. B-Tree 索引

3.1 结构

- Root → Branch → Leaf
- Leaf 存储 (key, ROWID)
- 有序

3.2 高度

- blevel = height - 1
- 0：Root+Leaf
- 1：1 Branch
- 2+：多 Branch

3.3 I/O

- 访问次数 = blevel + 1 + 表访问
- blevel 小好

4. 复合索引

4.1 列顺序

- 等值列在前
- 范围列在后
- 选择性高的在前
- 或查询模式

4.2 Jonathan 规则

- 等值优先
- 范围在后
- 评估业务查询

4.3 示例

-- 查询：WHERE deptno=10 AND salary>5000
CREATE INDEX idx_emp ON emp(deptno, salary);
-- 好：deptno 等值在前，salary 范围在后

5. 覆盖索引

5.1 原理

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

5.2 示例

-- 查询：SELECT deptno, ename FROM emp WHERE id=10
CREATE INDEX idx_emp_cover ON emp(id, deptno, ename);
-- 索引覆盖，无需回表

5.3 Jonathan 观点

- 覆盖索引性能好
- 但 DML 代价
- 评估

6. 跳跃扫描

6.1 INDEX SKIP SCAN

- 复合索引第一列未查
- CBO 跳跃
- 适合低基数第一列

6.2 示例

-- 索引 (gender, id)
SELECT * FROM emp WHERE id=10;
-- CBO 可能 SKIP SCAN

6.3 Jonathan 分析

- 第一列低基数
- SKIP SCAN 有效
- 评估

7. 反向键索引

7.1 原理

- 反转键值
- 分散热点
- RAC 友好

7.2 创建

CREATE INDEX idx_emp_rev ON emp(id) REVERSE;

7.3 限制

- 不支持范围查询
- 仅等值
- 评估

7.4 Jonathan 建议

- 顺序插入热点
- RAC 块争用
- 评估

8. 函数索引

8.1 原理

- 函数结果索引
- 避免计算

8.2 创建

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

8.3 查询

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

8.4 Jonathan 观点

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

9. 位图索引

9.1 适用

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

9.2 创建

CREATE BITMAP INDEX idx_emp_gender ON emp(gender);

9.3 优势

- 低基数高效
- AND/OR 高效
- 空间小

9.4 Jonathan 警告

- OLTP 死锁风险
- DML 代价大
- 评估

10. IOT（索引组织表）

10.1 原理

- 表存储在索引中
- 主键查询高效
- 节省空间

10.2 创建

CREATE TABLE emp (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100)
) ORGANIZATION INDEX;

10.3 Jonathan 应用

- 主键查询为主
- 减少回表
- 评估

11. Clustering Factor 优化

11.1 问题

- CF 接近行数
- 索引低效

11.2 优化

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

11.3 Jonathan 方法

-- 重建
CREATE TABLE emp_new AS 
SELECT * FROM emp ORDER BY deptno;
DROP TABLE emp;
RENAME emp_new TO emp;
-- 重建索引

12. 索引监控

12.1 使用监控

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

12.2 未使用索引

SELECT * FROM v$object_usage WHERE used='NO';

12.3 Jonathan 建议

- 监控一段时间
- 删除未使用
- 减少 DML 代价

13. 索引重建

13.1 评估

ANALYZE INDEX emp_idx VALIDATE STRUCTURE;
SELECT name, del_lf_rows, lf_rows FROM index_stats;

13.2 重建

ALTER INDEX emp_idx REBUILD ONLINE;

13.3 Jonathan 观点

- 不要定期重建
- 碎片严重才重建
- ONLINE

14. 不可见索引

14.1 创建

CREATE INDEX idx_test ON emp(salary) INVISIBLE;

14.2 测试

ALTER SESSION SET optimizer_use_invisible_indexes=true;

14.3 Jonathan 用途

- 测试索引影响
- 评估
- 删除或启用

15. 最佳实践

按查询设计：索引
复合索引：列顺序
覆盖索引：优化
CF：优化
监控：使用率
删除未使用：减少 DML
不要过度：平衡
重建：评估
统计信息：收集
测试：性能

16. 参考资料

[1] Jonathan Lewis, “Indexing Strategies”, https://jonathanlewis.wordpress.com [2] Jonathan Lewis, “Cost-Based Oracle Fundamentals”, Apress