Oracle 索引创建、维护与优化要点

Oracle 索引核心知识

创建原则

• 表修改频率：修改频率较低的表（如只读表）可多创建索引；修改频繁的表需慎重考虑。
• 数据筛选比例：当查询筛选数据量低于 15% 时，适合创建索引。
• NULL 值处理：索引通常不包含 NULL 值，针对 IS NOT NULL 的条件适合创建索引。
• 索引大小：建议最大索引尺寸控制在表大小的一半以下。
• 并行创建：可使用 PARALLEL 选项加快建索引速度。
• nologging 选项：创建索引时可结合 NOLOGGING 和 PARALLEL 提速，但需注意归档量变化。

特殊索引类型

Unusable Indexes（不可用索引）

• 优化器不会考虑，DML 也不会维护这些索引。
• 适用于大批量数据加载场景，加载完成后可将索引置为可用状态。
• 置为 unusable 时，Index Segment 会被删除。
• SKIP_UNUSABLE_INDEXES 参数决定 DML 是否维护被置为 unusable 的索引。
• 示例：

create index id_a on a (id) unusable;

Invisible Indexes（不可见索引）

• 优化器会忽略该索引，但 DML 语句仍会维护它。
• 单个分区索引不能置为 invisible 模式。
• 使用场景：
  1. 测试删除索引产生的影响。
  2. 创建临时索引以支持特定操作而不影响现有程序。
  3. 在已拥有索引的列上创建其他索引（同一时刻只能有一个可见）。
• 可在 session 级别设置 optimizer_use_invisible_indexes = true 来使用此类索引。
• 示例：

create index emp_id on emp(id) invisible;

基于函数的索引

• 包含 timestamp 等数据类型时，进行时间点恢复若新于系统恢复时间，可能导致索引失效。
• 可使用 ANALYZE 或 validate structure 确认状态。
• 可通过 dba_ind_expressions 查看表达式。

压缩索引 (Key-Compressed)

• 使用前缀和后缀值进行压缩，节省空间并提高性能。
• 适合非唯一值较多的引导列情况。
• 示例：

create index hr.emp_ename on emp(ename) tablespace users compress 1;
alter index hr.emp_ename rebuild nocompress;

索引维护与管理

• Rebuild vs Coalesce：Coalesce 功能较少但影响小，仅合并相同 height 的 leaf block，不缩减 index height。Rebuild 则完全重建。