Oracle 数据库索引原理与实战指南

四、索引创建命令示例

实际开发中，我们通常按以下语法创建不同类型的索引：

-- B-Tree 索引（默认）
CREATE INDEX idx_emp_last_name ON employees(last_name);

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

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

-- 位图索引（适用于低基数列）
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

-- 函数索引（注意查询时必须使用相同函数）
CREATE INDEX idx_upper_name ON employees(UPPER(last_name));

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

-- 逆序索引（解决自增 ID 热点问题）
CREATE INDEX idx_emp_id_rev ON employees(employee_id) REVERSE;

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

-- 全局分区索引
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 最常用的索引类型。其特点是物理层通常为 2-3 层，逻辑上也是三层。叶子块数据有序排列，从左向右递增。适合 OLTP 场景下的大量增删改操作，但不支持包含 OR 操作符的查询。如果索引列的值都存储在索引中，建立组合索引可直接满足查询需求，无需回表访问，进一步减少 IO。

2. 位图索引

专为低基数列（如性别、状态）设计。相比 B-Tree，位图索引体积更小，适合检索超过半数行的场景。但它在批处理操作中表现较好，高并发事务处理中应避免使用，因为多个会话同时插入会导致锁竞争。

3. 函数索引

当查询条件对列应用了函数（如 WHERE UPPER(job)='MGR'）时，普通索引无法命中。此时需创建基于函数的索引。但需注意，索引越多，DML 语句（INSERT/UPDATE/DELETE）的执行开销越大，实施前需评估存储空间与维护成本。

4. 索引组织表 (IOT)

普通表结构是'索引 -> ROWID -> 数据'，而 IOT 将数据直接存储在索引的叶子节点中。查询主键时无需回表，极大提升了主键查询性能，适合主键查询为主的场景。

5. 分区索引

分为本地和全局两种。本地分区索引与表分区同步，维护方便；全局分区索引独立于表分区，适合跨分区的范围查询，但需手动维护。选择哪种取决于具体的查询模式和分区策略。

6. 逆序索引

针对自增 ID 插入导致的索引尾部热点（所有新数据都挤在最后一个叶子块），逆序索引将键值反转（如 123 变为 321），使插入位置分散在索引各处，缓解热点冲突，提升写入性能。

在实际应用中，没有一种索引能解决所有问题。我们需要根据查询模式、数据分布及读写比例，综合权衡选择最合适的索引策略。