Oracle 索引核心原理、类型详解与创建实战

三、创建命令示例

以下 SQL 语句均适用于 Oracle 环境，实际使用时请替换表名和字段名。

-- 1. B-Tree 索引
CREATE INDEX idx_emp_last_name ON employees(last_name);

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

-- 3. 组合索引 (复合索引)
CREATE INDEX idx_emp_dept_job ON employees(department_id, job);

-- 4. 位图索引
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

-- 5. 函数索引
CREATE INDEX idx_upper_name ON employees(UPPER(last_name));

-- 6. 索引组织表 (IOT)
CREATE TABLE employees_iot (
    employee_id NUMBER PRIMARY KEY,
    last_name VARCHAR2(25),
    department_id NUMBER
) ORGANIZATION INDEX;

-- 7. 位图连接索引
CREATE BITMAP INDEX idx_sales_emp ON sales(sale_id)
TABLE employees (employee_id)
WHERE employees.status = 'ACTIVE';

-- 8. 逆序索引
CREATE INDEX idx_emp_id_rev ON employees(employee_id) REVERSE;

-- 9. 本地分区索引
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

-- 10. 全局分区索引
CREATE INDEX idx_sales_amount ON sales(amount)
GLOBAL PARTITION BY RANGE (amount) (
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000)
);

四、各类索引详解

1. B-Tree 索引

Oracle 最常见的索引类型，默认未指定类型时自动创建。适合 OLTP 场景的大量增删改操作。

特点：

• 典型的树状结构，每个节点都是数据块。
• 逻辑上通常为三层，物理上视数据量而定。
• 叶子块数据排序，从左向右递增。
• 分支块和根块存放索引范围。

技巧： 索引列的值都存储在索引中。建立组合索引可直接满足查询，无需访问表，从而减少 IO。注意，包含 OR 操作符的查询可能无法有效利用 B-Tree 索引。

2. 唯一索引

基于 B 树结构，配合 UNIQUE 约束使用。允许一个 NULL 值（NULL 不参与唯一性比较），创建主键时会自动生成。

3. 组合索引

多列的 B-Tree 索引。遵循最左前缀原则：查询条件必须包含最左列才能命中索引。列的顺序至关重要，应将与高频查询条件匹配的列放在前面。

4. 位图索引

专为低基数列设计的特殊索引（如性别、状态）。

特点：

• 非常适合 OR 操作符的查询。
• 仅当基数较少时才建议建立。
• 不适合高并发写入，多个会话同时插入行时应避免使用。

技巧： 对于基数低的列（如性别只有男/女），位图索引尺寸远小于 B-Tree，检索半行以上数据时仍高效。合并多个位图索引可显著提升性能。固定长度数据类型比可变长度更适合位图索引。

5. 函数索引

在表达式或函数上创建的索引，而非原始列。若没有函数索引，对列执行函数的查询将无法使用该列的普通索引。

例如，SELECT * FROM emp WHERE UPPER(job)='MGR' 无法使用 JOB 列上的普通索引，除非建立基于函数的索引：

CREATE INDEX EMP$UPPER_JOB ON emp(UPPER(job));

注意事项：

• 限制该列上使用的函数类型。
• 评估额外存储空间。
• 索引越多，DML（INSERT/UPDATE/DELETE）性能开销越大。

6. 索引组织表 (IOT)

表数据直接存储在主键索引的叶子节点中，而非单独存储数据块。查询主键时无需回表，直接获取数据。

• 普通表：索引 → 数据块 (ROWID) → 数据行
• IOT 结构：索引 → 数据行 (直接存储)

7. 位图连接索引

位图索引的扩展，直接关联多表的连接条件。专为数据仓库的星型模型设计（如事实表 JOIN 维度表）。

8. 逆序索引

反转索引键值（如 123 → 321）。主要用于解决自增 ID 插入时的索引尾部热点问题，避免频繁修改最后一个叶子块，提升高并发写入性能。

9. 本地分区索引

索引分区与表分区一致（表分区 1 → 索引分区 1）。表分区操作（如 ADD PARTITION）会自动同步到索引，维护成本低。

10. 全局分区索引

索引分区独立于表分区，一个索引分区可能包含多个表分区的数据。适合范围查询，但表分区操作后需手动重建索引，维护较复杂。