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 模式。
- 使用场景:
- 测试删除索引产生的影响。
- 创建临时索引以支持特定操作而不影响现有程序。
- 在已拥有索引的列上创建其他索引(同一时刻只能有一个可见)。
- 可在 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 则完全重建。