ALTER INDEX COALESCE不能减少叶块碎片，因为它仅合并相邻且空闲空间充足的叶块，不移动数据、不重排键序、不释放空块，对块内空洞和ITL争用无效；真正降低leaf_blocks须用REBUILD。
ALTER INDEX COALESCE 为什么不能减少叶块碎片
alter index ... coalesce 只合并同一索引段内**相邻的、空闲空间足够多的叶块**，但不会移动数据行、不重排键顺序、也不释放空块到段管理结构中。它本质上是“就地整理”，对已存在的叶块内部碎片（比如因频繁 delete 或 update 导致的块内空洞）完全无效。
常见误解是：只要执行了 COALESCE，叶块数就会下降、blevel 会降低、扫描性能会变好——实际几乎从不发生。
- 只影响高水位线（HWM）以下、且块间有连续空闲空间的叶块区域
- 不改变索引结构层级（
blevel不变） - 不回收已分配但空闲的叶块（
num_rows不变，leaf_blocks通常也不变） - 无法修复因
INITRANS设置过低或并发更新导致的 ITL 争用型碎片
真正能减少叶块碎片的操作只有 REBUILD
要实质性降低 leaf_blocks、压缩物理存储、重排键值顺序、清理 ITL 和空洞，必须用 ALTER INDEX ... REBUILD。它会分配新段、逐行重写索引项，并按当前 PCTFREE 和排序规则重新填充叶块。
注意：默认 REBUILD 是在线操作（Oracle 12c+），但会持有 DL（Direct Load）锁，阻塞 DML 的 DDL 类变更；若加 ONLINE，则允许并发 DML，但代价是生成更多重做、临时段占用更高、耗时更长。
- 想最小化空间：用
REBUILD PCTFREE 0（慎用，后续无更新余量） - 兼顾后续更新：设
PCTFREE 10～20，比原值略低即可 - 跨表空间迁移：加
TABLESPACE new_ts，顺便完成物理重组 - 避免日志：加
NOPARALLEL NOLOGGING（前提是可接受无法归档恢复）
怎么判断叶块碎片是否真严重
别只看 DBA_INDEXES.leaf_blocks 或 INDEX_STATS 的 DEL_LF_ROWS。关键要看运行时表现：
- 执行
SELECT /*+ INDEX(t idx) */ COUNT(*) FROM t WHERE ...时逻辑读远高于估算的叶块数 × 2 -
V$SEGMENT_STATISTICS中该索引的gc cr block lost或db block gets异常高 - AWR 报告里该索引相关 SQL 的 “buffer is pinned count” 持续上升
-
ANALYZE INDEX ... VALIDATE STRUCTURE后查INDEX_STATS，发现HEIGHT > 4且LF_ROWS / LF_BLKS
如果只是 DEL_LF_ROWS / LF_ROWS > 30% 但查询响应稳定，大概率不需要干预。
COALESCE 还有没有用的场景
有，但非常窄：仅适用于刚批量 DELETE 了大量有序键值（比如删掉半年前的分区数据），且这些删除集中在索引末尾几个相邻叶块，此时 COALESCE 可能把它们合并成 1 块并释放回 freelists —— 但这种情况在 OLTP 系统里极少出现。
更现实的用途是“轻量预热”：在业务低峰期跑一遍 COALESCE，成本极低（基本不锁表、不占 temp），万一凑巧清掉一两个块，算白捡；但绝不能把它当碎片治理主力。
真正需要控制碎片的地方，是建索引时就定好 PCTFREE、避免在高并发更新字段上建非必要索引、定期用 REBUILD 替代盲目 COALESCE —— 后者名字带“合并”，干的却是最保守的活。
