DB / Oracle / SQL 开发

Oracle 索引（Index）详解

2026.09.06

Oracle 索引（Index）详解

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

1. 概述

索引 提升查询性能[1]：

类型说明
B-Tree默认，平衡树
Bitmap位图，低基数
Reverse Key反转键
Function-Based函数索引
Domain自定义
IOT索引组织表

2. B-Tree 索引

2.1 创建

-- 普通索引
CREATE INDEX idx_emp_name ON employees(last_name);

-- 复合索引
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

-- 唯一索引
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

2.2 结构

Root（根块）
  ├─ Branch（分支块）
  │   ├─ Branch
  │   │   ├─ Leaf（叶子块）→ 行数据 ROWID
  │   │   └─ Leaf
  │   └─ Branch
  └─ Branch

2.3 特点

  • 适合高基数列
  • 支持 =, >, <, BETWEEN, LIKE（前缀）
  • DML 开销中等
  • 索引不存 NULL

3. Bitmap 索引

3.1 创建

CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
CREATE BITMAP INDEX idx_emp_status ON employees(status);

3.2 结构

值 'M':  1010010101
值 'F':  0101101010

3.3 特点

  • 适合低基数列（< 1%）
  • 空间小
  • 高效 AND/OR 操作
  • DML 开销大
  • 适合 OLAP，不适合 OLTP

4. Reverse Key 索引

CREATE INDEX idx_emp_id_rev ON employees(employee_id) REVERSE;

4.1 特点

  • 反转键值（123 → 321）
  • 减少热点块
  • 不支持范围查询
  • 适合 RAC 序列生成

5. Function-Based 索引

5.1 创建

-- 函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- 使用
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- 表达式索引
CREATE INDEX idx_emp_sal_annual ON employees(salary * 12);

-- 使用
SELECT * FROM employees WHERE salary * 12 > 60000;

5.2 注意

  • 必须启用 QUERY REWRITE
  • 函数必须确定性
  • 收集统计信息

6. 复合索引

6.1 列顺序

-- 好：where dept_id = 10 AND salary > 5000
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

-- 等值条件在前
-- 范围条件在后

6.2 前缀扫描

-- 索引 (a, b, c)
SELECT * FROM t WHERE a = 1;              -- 使用
SELECT * FROM t WHERE a = 1 AND b = 2;    -- 使用
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;  -- 使用
SELECT * FROM t WHERE b = 2;              -- 不使用
SELECT * FROM t WHERE a = 1 AND c = 3;    -- 部分使用

7. 索引组织表（IOT）

7.1 创建

CREATE TABLE employees_iot (
  id NUMBER,
  name VARCHAR2(100),
  salary NUMBER,
  CONSTRAINT pk_emp_iot PRIMARY KEY (id)
) ORGANIZATION INDEX;

7.2 特点

  • 表数据存储在索引中
  • 按主键排序
  • 适合主键查询
  • 节省空间

8. 索引选项

8.1 ONLINE

-- 在线创建（不阻塞 DML）
CREATE INDEX idx_emp_name ON employees(last_name) ONLINE;

8.2 PARALLEL

-- 并行创建
CREATE INDEX idx_emp_name ON employees(last_name) PARALLEL 4;

8.3 NOLOGGING

-- 不记录 redo（快）
CREATE INDEX idx_emp_name ON employees(last_name) NOLOGGING;

8.4 COMPRESS

-- 压缩
CREATE INDEX idx_emp_dept ON employees(dept_id, dept_name) COMPRESS 1;

8.5 TABLESPACE

-- 指定表空间
CREATE INDEX idx_emp_name ON employees(last_name) 
TABLESPACE indx;

9. 索引维护

9.1 重建

ALTER INDEX idx_emp_name REBUILD;
ALTER INDEX idx_emp_name REBUILD ONLINE;
ALTER INDEX idx_emp_name REBUILD TABLESPACE indx;

9.2 合并

ALTER INDEX idx_emp_name COALESCE;

9.3 统计信息

EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT', 'idx_emp_name');

9.4 监控使用

-- 启用监控
ALTER INDEX idx_emp_name MONITORING USAGE;

-- 查看
SELECT * FROM v$object_usage;

-- 停止
ALTER INDEX idx_emp_name NOMONITORING USAGE;

9.5 删除

DROP INDEX idx_emp_name;

10. 查看索引

SELECT 
  index_name,
  index_type,
  table_name,
  uniqueness,
  status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- 索引列
SELECT 
  index_name,
  column_name,
  column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES';

11. 索引选择

11.1 决策因素

  • 数据基数
  • 查询频率
  • DML 频率
  • 表大小
  • 存储空间

11.2 选择矩阵

场景推荐索引
高基数等值B-Tree
低基数Bitmap
序列生成Reverse Key
函数查询Function-Based
主键查询IOT
范围查询B-Tree

12. 何时不应建索引

  • 小表
  • 极少查询的列
  • 高频 DML 列
  • WHERE 不使用的列
  • 基数极低（如布尔）

13. 常见坑与排错

13.1 索引不使用

-- 1. 函数阻止
WHERE UPPER(name) = 'SMITH'  -- 索引 name 不用
-- 修复：函数索引 UPPER(name)

-- 2. 隐式转换
WHERE id = '100'  -- 字符转数字，索引不用
-- 修复：WHERE id = 100

-- 3. NULL 查询
WHERE name IS NULL  -- 索引不存 NULL
-- 修复：bitmap 索引 或 默认值

-- 4. 统计信息过期
EXEC DBMS_STATS.GATHER_TABLE_STATS(...);

-- 5. CBO 选择全表扫描
-- 加 HINT
SELECT /*+ INDEX(e idx_emp_name) */ * FROM employees e WHERE ...

13.2 索引碎片

-- 查看高度
ANALYZE INDEX idx_emp_name VALIDATE STRUCTURE;
SELECT height, name FROM index_stats;

-- 重建
ALTER INDEX idx_emp_name REBUILD;

13.3 ORA-01502: 索引失效

-- 原因：UNUSABLE 状态
-- 修复：重建
ALTER INDEX idx_emp_name REBUILD;

14. 最佳实践

  1. 主键自动建索引：默认
  2. 外键加索引：避免锁
  3. 查询频繁列建索引：性能
  4. 高基数用 B-Tree：标准
  5. 低基数用 Bitmap：OLAP
  6. 函数列用 FBI：避免全表扫描
  7. 复合索引列顺序：等值在前
  8. 定期维护：重建/统计
  9. 监控使用：删除无用
  10. 避免过度索引：影响 DML

15. 参考资料

[1] Oracle Database Concepts 19c, “Indexes” https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/indexes.html