oracle 索引

Oracle 数据库中的索引是优化查询性能的重要工具，其类型多样，适用于不同场景。以下是 Oracle 索引的主要分类及特点：

1.B-Tree 索引（平衡树索引）

• 特点：

• 默认索引类型，树形结构（根、分支、叶块），支持等值查询和范围查询。

• 适用场景：

  • 高基数列（唯一值多，如主键、身份证号）。

  • 频繁的 WHERE、ORDER BY、JOIN 操作。

• 语法：

CREATE INDEX idx_emp_name ON employees (last_name);

 2. 位图索引（Bitmap Index）

• 特点：

• 为低基数列（唯一值少，如性别、状态）存储位图，支持高效的逻辑运算（AND/OR/NOT）。

• 适用场景：

  • 数据仓库或 OLAP 系统（读多写少）。

  • 多列组合的低基数查询（如 WHERE status='A' AND dept='IT'）。

• 注意事项：

  • 不适合频繁更新的 OLTP 系统（位图锁粒度大）。

• 语法：

 CREATE BITMAP INDEX idx_emp_gender ON employees (gender);

3. 函数索引（Function-Based Index）

• 特点：

• 基于表达式或函数（如 UPPER(name)、salary*12）构建索引。

• 适用场景：

  • 查询条件包含函数或计算（如 WHERE UPPER(last_name) = 'SMITH'）。

  • 优化大小写不敏感的查询。

• 语法：

CREATE INDEX idx_emp_upper_name ON employees (UPPER(last_name));

4. 反向键索引（Reverse Key Index） 

• 特点： 

• 将索引键值的字节顺序反转（如 123 → 321），减少索引块争用。

• 适用场景：

  • 避免索引热点（如序列主键的并发插入场景）。

• 注意事项：

  • 不支持范围查询（如 BETWEEN）。

• 语法： 

 CREATE INDEX idx_emp_id_rev ON employees (employee_id) REVERSE;

 5. 组合索引（Composite Index）

• 特点： 

•   基于多列组合构建索引（最多 32 列）。

• 适用场景：

  • 多列联合查询（如 WHERE dept_id=10 AND salary>5000）。

  • 覆盖索引（索引包含查询所需的所有列）。

• 优化技巧：

  • 高频查询的列放在索引最左侧。

• 语法：

 CREATE INDEX idx_emp_dept_sal ON employees (dept_id, salary);

 6. 唯一索引（Unique Index）

• 特点：

• 确保索引列的值唯一（主键自动创建唯一索引）。

• 适用场景：

  • 强制唯一性约束（如用户名、邮箱）。

• 语法：

 CREATE UNIQUE INDEX idx_emp_email ON employees (email);

 7. 分区索引

(1) 本地分区索引（Local Partitioned Index）

• 特点：
  索引与表的分区一一对应，每个分区独立维护索引。

• 适用场景：

  • 分区表（如按时间范围分区）。

  • 分区维护操作（如 TRUNCATE）自动更新索引。

(2) 全局分区索引（Global Partitioned Index）

• 特点：
  索引的分区方式独立于表，可跨表分区。

• 适用场景：

  • 跨分区的查询优化。

• 注意事项：

  • 分区表结构变更时需重建索引。

8. 压缩索引（Compressed Index） 

• 特点：

• 压缩索引键的前缀重复值，减少存储空间。

• 适用场景：

  • 组合索引的前缀列重复率高（如 (dept_id, employee_id)，dept_id 重复）。

• 语法：

CREATE INDEX idx_emp_compressed ON employees (dept_id, employee_id) COMPRESS;

索引选择建议

注意事项

1. 索引维护成本：

   • 索引会降低 DML（INSERT/UPDATE/DELETE）性能。

   • 定期重建碎片化索引（ALTER INDEX ... REBUILD）。

2. 监控使用情况：

   • 通过 DBA_INDEXES、V$OBJECT_USAGE 查看索引状态。

3. 避免过度索引：

   • 仅对高频查询列创建索引。

合理选择索引类型可显著提升查询性能，但需结合数据分布、业务场景和系统负载综合评估。