在Oracle数据库的索引维护工作中，REBUILD和COALESCE是两条最常用的命令，但很多运维人员对它们的理解停留在“都能整理碎片”的层面，一旦索引出现性能下降就随手执行REBUILD，结果可能带来不必要的锁等待、临时表空间耗尽，甚至造成业务中断。本文将从B树索引的内部结构入手，深入分析两种操作的本质区别，并通过具体示例给出选择建议。
一、索引碎片产生的根源以及两种操作的机制差异
Oracle的B树索引由根块、分支块和叶块组成。当向表中插入新数据时，索引键值会按顺序写入叶块。如果某个叶块已经填满，而新键值又必须插入到该块中间，Oracle就会发生叶块分裂：原有的叶块被拆分成两个块，每个块大约保留一半的数据。这种分裂会导致索引叶块链上的键值不再连续，而且许多块只使用了部分空间。随着时间的推移，大量插入、更新和删除操作会让索引产生两种碎片：一是块内部的空间浪费，即每个块中有效数据占比下降；二是块之间的物理顺序与逻辑顺序不一致，导致范围扫描时需要更多的I/O。
REBUILD的本质是“推倒重来”。它创建一个全新的索引段，按照当前表中的数据重新排序并填充到新段中，完成后删除旧段。这个过程中，所有碎片都被消除，索引的高水位线会降低到实际数据量对应的位置，多余的空间会被释放回表空间。但代价是REBUILD需要额外的临时空间来容纳新索引，并且在重建期间对索引加锁，如果不使用ONLINE选项，DML操作会被阻塞。
COALESCE则完全不同。它不创建新段，而是扫描现有索引的叶块，将相邻的、填充率较低的叶块合并成一个块。合并后，被释放的叶块从叶块链中摘除，但索引段的物理大小不变，高水位线也不下降。也就是说，COALESCE只是把数据在现有段内部进行了“压缩”，消除了块之间的碎片，但不会把空间归还给表空间。所以COALESCE更像是一次内部整理，而不是重建。
二、锁行为、空间释放与性能影响的对比
锁行为是两者最直观的区别。REBUILD是一条DDL语句，默认情况下需要获取索引上的排他锁，意味着在重建期间任何对该索引的DML操作都会被阻塞。即使使用ALTER INDEX ... REBUILD ONLINE，虽然允许DML并发进行，但在重建的初始阶段和结束阶段仍然需要短暂的锁，并且需要额外的日志和临时空间来记录并发修改。COALESCE并不是DDL，它只是对索引执行内部维护，锁的粒度很小，通常只锁定当前正在合并的少数几个块，其他块上的DML不受影响。因此，对于7x24小时运行的核心业务系统，COALESCE对可用性的影响远小于REBUILD。
空间释放方面，REBUILD能够显著降低高水位线，将索引段收缩到接近实际数据量的大小，从而减少表空间的占用。如果索引曾经因为大量删除而变得很大，REBUILD后空间可以明显回收。COALESCE则不同，它只合并叶块，不会改变段头中的高水位线标记，因此即使合并后很多块变为空闲，这些空闲块仍然属于该索引段，不会释放给表空间。不过，这些空闲块可以被后续的插入操作重新利用，所以从长期来看COALESCE能减少段内部的空闲块数，提高空间复用率。
性能影响同样不对称。REBUILD需要全表扫描或索引全扫描来构建新索引，并产生大量的排序和写操作，I/O消耗很大。如果索引很大，REBUILD可能花费数小时甚至更久，并且需要足够的临时表空间。COALESCE只扫描叶块链并进行少量块移动，消耗的资源远小于REBUILD，执行速度通常快得多。在碎片率不高的情况下，COALESCE几分钟就能完成，而REBUILD可能是几十分钟。
三、实际场景下的选择策略与操作示例
判断该选择哪种操作，首先要量化索引的碎片程度。Oracle提供了ANALYZE INDEX ... VALIDATE STRUCTURE命令，执行后可以从INDEX_STATS视图中获取相关指标。例如下面的SQL可以用来分析索引SCOTT.EMP_IDX的碎片情况：
ANALYZE INDEX SCOTT.EMP_IDX VALIDATE STRUCTURE;
SELECT HEIGHT,
DEL_LF_ROWS,
LF_ROWS,
ROUND(DEL_LF_ROWS / NULLIF(LF_ROWS, 0) * 100, 2) AS DEL_PCT,
PCT_USED
FROM INDEX_STATS
WHERE NAME = 'EMP_IDX';
上述查询中，DEL_PCT表示被删除的叶行占总叶行的百分比，PCT_USED表示叶块的空间平均使用率。如果DEL_PCT超过20%或者PCT_USED低于60%，并且索引尺寸非常大，同时你确实需要把空间释放回表空间以缓解表空间压力，那么REBUILD是合理的选择。如果碎片率不高，只是范围扫描性能有所下降，而且不想影响在线业务，COALESCE是更稳妥的方案。
下面给出两种操作的具体命令。执行REBUILD ONLINE的示例：
ALTER INDEX SCOTT.EMP_IDX REBUILD ONLINE;
执行COALESCE的示例：
ALTER INDEX SCOTT.EMP_IDX COALESCE;
需要注意的是，REBUILD ONLINE虽然允许DML，但前提是表上没有未提交的长事务，否则可能因等待锁而挂起。另外，REBUILD期间需要保证有足够的临时表空间，否则会报ORA-01652错误。COALESCE则没有这些要求，但它在合并叶块时如果遇到大量并发插入，可能会因为叶块被反复填满而需要多次执行才能达到理想效果。
下表总结了两种操作的核心差异，方便快速参考：
| 对比维度 | REBUILD | COALESCE |
|---|---|---|
| 操作类型 | DDL，重建索引段 | 维护操作，合并叶块 |
| 锁粒度 | 排他锁（ONLINE时短暂锁） | 块级锁，影响小 |
| 空间释放 | 降低高水位线，释放空间给表空间 | 不降低高水位线，空间保留在段内 |
| 执行速度 | 慢，I/O和排序开销大 | 快，资源消耗小 |
| 适用场景 | 重度碎片、需要回收空间、维护窗口充足 | 轻度碎片、在线业务敏感、快速整理 |
总之，REBUILD和COALESCE不是可以随意替换的操作。理解它们对索引段和高水位线的不同影响，结合碎片统计数据和业务窗口，才能做出最优的维护决策。如果只是周期性整理轻度碎片，COALESCE完全够用；只有遇到索引严重膨胀、删除比例极高且必须回收空间时，才需要考虑REBUILD。