DB / Oracle / SQL 开发

Oracle 索引类型与应用

2026.09.06

Oracle 索引类型与应用

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

1. 概述

Oracle 索引类型与应用[1]：

类型：

  • B-Tree
  • Bitmap
  • Reverse
  • Function-based
  • Domain
  • Bitmap Join

详细见：Oracle 索引优化策略。

2. B-Tree 索引

2.1 默认

CREATE INDEX idx_emp_name ON employees(name);

2.2 复合

CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

2.3 唯一

CREATE UNIQUE INDEX idx_emp_email ON employees(email);

2.4 适用

  • 高选择性
  • OLTP

3. Bitmap 索引

3.1 创建

CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

3.2 适用

  • 低选择性
  • 数据仓库
  • 不频繁更新

3.3 优势

  • 节省空间
  • 多列 AND/OR 高效

3.4 不适用

  • OLTP
  • 频繁更新

4. Reverse Key 索引

4.1 创建

CREATE INDEX idx_emp_id_rev ON employees(id) REVERSE;

4.2 适用

  • 序列生成 ID
  • 热点块
  • 等值查询

4.3 不支持

  • 范围查询

5. Function-Based 索引

5.1 函数

CREATE INDEX idx_emp_upper ON employees(UPPER(name));
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';

5.2 表达式

CREATE INDEX idx_emp_sal ON employees(salary * 1.1);

5.3 案例

-- 复杂
CREATE INDEX idx_emp_dept_func ON employees(
  CASE WHEN dept_id = 10 THEN salary ELSE NULL END
);

6. 复合索引

6.1 列顺序

-- 高选择性在前
CREATE INDEX idx ON employees(dept_id, salary, hire_date);

6.2 选择

  • WHERE 频繁
  • 高选择性
  • 排序

6.3 监控

ALTER INDEX idx MONITORING USAGE;
-- 业务运行
SELECT * FROM v$object_usage;

7. 唯一索引 vs 主键

7.1 主键

ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (id);
-- 自动创建唯一索引

7.2 唯一索引

CREATE UNIQUE INDEX uk_emp_email ON employees(email);

7.3 选择

  • 主键：业务标识
  • 唯一索引：业务约束

8. 全文索引

8.1 CONTEXT

CREATE INDEX idx_docs_text ON docs(content) INDEXTYPE IS CTXSYS.CONTEXT;

SELECT * FROM docs WHERE CONTAINS(content, 'oracle', 1) > 0;

8.2 CTXCAT

CREATE INDEX idx_items_cat ON items(name, description) 
  INDEXTYPE IS CTXSYS.CTXCAT;

详细见：Oracle 全文检索。

9. Domain 索引

9.1 Spatial

CREATE INDEX idx_geo ON locations(geometry) 
  INDEXTYPE IS MDSYS.SPATIAL_INDEX;

9.2 自定义

- 用户定义类型
- ODCI 接口

10. Bitmap Join

CREATE BITMAP INDEX idx_sales_cust_city ON sales(c.customer_city)
  FROM sales s, customers c
  WHERE s.cust_id = c.id;

11. 索引组织表（IOT）

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

详细见：Oracle 索引聚簇因子与优化。

12. 索引压缩

-- 前缀压缩
CREATE INDEX idx_emp ON employees(dept_id, name) COMPRESS 1;

-- 高级压缩
CREATE INDEX idx_emp ON employees(dept_id, name) COMPRESS ADVANCED LOW;

详细见：Oracle 表压缩技术。

13. 索引管理

13.1 重建

ALTER INDEX idx REBUILD ONLINE PARALLEL 4;
ALTER INDEX idx NOPARALLEL;

13.2 合并

ALTER INDEX idx COALESCE;

13.3 失效

ALTER INDEX idx UNUSABLE;
ALTER INDEX idx REBUILD;

13.4 统计

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

14. 索引选择

14.1 选择性

selectivity = num_distinct / num_rows
> 0.1（10%）：适合 B-Tree
< 0.1：考虑 Bitmap

14.2 列基数

- 低基数：Bitmap
- 高基数：B-Tree

14.3 查询模式

- 等值：B-Tree/Bitmap
- 范围：B-Tree
- 函数：Function-based
- 模糊：CONTEXT

15. 索引监控

15.1 使用

ALTER INDEX idx MONITORING USAGE;
SELECT * FROM v$object_usage;
ALTER INDEX idx NOMONITORING USAGE;

15.2 状态

SELECT owner, index_name, status FROM dba_indexes WHERE status != 'VALID';

15.3 碎片

ANALYZE INDEX idx VALIDATE STRUCTURE;
SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows / lf_rows) * 100 AS pct_deleted
FROM index_stats;

16. 常见坑与排错

16.1 索引未使用

-- 1. 函数阻止
SELECT * FROM t WHERE UPPER(name) = 'X';  -- 需函数索引

-- 2. 隐式转换
SELECT * FROM t WHERE id = '100';  -- 字符转数字

-- 3. NULL
SELECT * FROM t WHERE col IS NULL;  -- 索引不含 NULL

-- 4. 统计信息

16.2 索引失效

-- 1. DDL
ALTER TABLE ... MOVE;

-- 2. 重建
ALTER INDEX ... REBUILD ONLINE;

16.3 索引过多

- DML 慢
- 空间占用
- 监控使用
- 删除无用

17. 最佳实践

  1. B-Tree 高选择性：默认
  2. Bitmap 低选择性：仓库
  3. Function 函数：函数查询
  4. Reverse 热点：序列
  5. 复合顺序：选择性
  6. 监控使用：删除无用
  7. 定期重建：碎片
  8. 索引覆盖：减少回表
  9. 测试验证：效果
  10. 文档化：维护

18. 参考资料

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