Oracle索引段空间碎片整理方法 如何执行COALESCE合并优化

索引占用空间远大于实际数据量，是DBA日常运维中一个相当典型的“空间假象”。你可能会发现，一个仅有几万行记录的表，其索引段却膨胀到了几个GB。这背后的根本原因，通常不是数据本身变多了，而是频繁的DELETE或UPDATE操作，在B树索引内部留下了大量“空洞”——也就是那些已经空闲、但仍被索引段持有的叶块和分支块。DBA_SEGMENTS.BYTES视图会忠实地统计这些已分配但无效的块，从而造成了索引“虚大”的观感。首先要明确一点：COALESCE操作的目标是整理这些内部碎片，它并不会将空间释放回表空间。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

什么时候该用 ALTER INDEX ... COALESCE

这个命令的适用场景其实非常聚焦。它并非万能，对于UNUSABLE或INVALID状态的索引是无能为力的，它只针对那些“状态健康但内部松散”的索引。通常，以下几种情况是触发COALESCE的典型信号：

• 表上经历过大量非批量的DELETE操作，尤其是按非主键条件进行的删除，之后又未进行索引重建。
• 查询USER_INDEXES视图时，发现LEAF_BLOCKS（叶块数量）异常偏高，而DISTINCT_KEYS（唯一键值数）却相对很小，两者比例失衡。
• 执行ANALYZE INDEX ... VALIDATE STRUCTURE后，从INDEX_STATS中看到DEL_LF_ROWS / LF_ROWS的比值超过了20%。
• 监控系统频繁报警显示某个索引段空间增长过快，但实际业务数据写入量并没有相应幅度的突增。

COALESCE 和 REBUILD 的关键区别别搞混

这是最容易混淆和踩坑的地方。两者虽然都用于索引维护，但目标、行为和代价截然不同：

• COALESCE：可以理解为“碎片整理”。它只合并物理上相邻的、有空闲空间的叶块，不会移动数据到新的存储段，也不改变索引的物理位置。整个过程是“在线”的，仅持有低级别的SS（共享子）锁，对正在进行的DML操作影响微乎其微。
• REBUILD：相当于“推倒重来”。它会创建一个全新的索引段，将有效数据完整地写入其中，然后删除旧的索引段，从而彻底释放空间。这需要额外的空闲存储空间（大约与原索引相当），并在操作期间持有EXCLUSIVE级别的锁（全局索引锁表，局部索引锁分区），会导致依赖该索引的SQL游标失效。
• 选择策略：如果索引碎片化已经非常严重（例如DEL_LF_ROWS占比超过40%），COALESCE的整理效果会非常有限。此时，更明智的选择是使用REBUILD ONLINE，而不是强行使用COALESCE。
• 空间回收：最关键的一点，COALESCE不会降低索引段的高水平线（HWM），因此DBA_SEGMENTS.BYTES显示的大小可能不会变化；而REBUILD会重置HWM，空间会被真实地回收并返还给表空间。

实操命令与验证步骤

执行前，务必确认目标索引状态为VALID，并且当前用户拥有ALTER ANY INDEX权限或该索引的对象权限：

ALTER INDEX jingyu.IDX_T_01 COALESCE;

执行之后，如何验证效果呢？可以从以下几个维度入手：

• 查看段大小：SELECT bytes/1024/1024 FROM dba_segments WHERE segment_name = 'IDX_T_01' AND owner = 'JINGYU';。但请注意，如前所述，多数情况下BYTES可能不变，这并不代表操作失败。
• 分析叶块使用率：执行ANALYZE INDEX jingyu.IDX_T_01 VALIDATE STRUCTURE;，然后查询SELECT name, lf_rows, del_lf_rows, pct_used FROM index_stats;。重点关注pct_used（叶块使用百分比）是否有所提升。
• 对比执行计划：对使用该索引的典型查询重新执行EXPLAIN PLAN，观察访问谓词和过滤谓词是否更稳定，或者通过实际执行查看consistent gets（逻辑读）是否下降。

然而，比执行操作更难的，往往是判断“这个索引到底值不值得去动”。一个更根本的问题是：这个索引真的被频繁使用吗？建议结合V$OBJECT_USAGE监控索引的使用情况，或者查询DBA_HIST_SQLSTAT等历史视图，分析相关SQL的逻辑读趋势。否则，盲目执行COALESCE，很可能只是在运维日志里多了一行记录，对数据库的实际性能提升并无帮助。