Oracle索引段“虚大”本质是索引碎片，源于频繁DELETE/UPDATE导致B树中大量空闲叶块未释放，DBA_SEGMENTS.BYTES仍统计这些无效块；COALESCE仅在线整理段内碎片、不降HWM、不释放空间，适用于DEL_LF_ROWS/LF_ROWS>20%且索引状态VALID的场景。
Oracle索引段“虚大”本质是索引碎片，不是数据量问题
索引段占用空间远超实际数据所需（比如一个只有几万行的表，索引占几个gb），根本原因不是数据多，而是频繁 delete 或 update 导致 b 树索引内部产生大量空闲叶块（leaf block）和分支块空洞。这些块仍被索引段持有，但实际没存有效键值，dba_segments.bytes 会照常统计，造成“虚大”。coalesce 不释放空间给表空间，只整理段内碎片——这点必须先认清。
什么时候该用 ALTER INDEX ... COALESCE
适用场景非常明确：UNUSABLE 状态不能用它，INVALID 也不行，它只处理“可用但松散”的索引。典型触发条件包括：
- 表上执行过大量单条或小批量
DELETE（尤其非主键条件删除），且未重建索引 -
SELECT index_name, leaf_blocks, distinct_keys, clustering_factor FROM user_indexes显示leaf_blocks异常高，但distinct_keys很小 - 执行
ANALYZE INDEX ... VALIDATE STRUCTURE后查INDEX_STATS，发现DEL_LF_ROWS / LF_ROWS> 20% - 索引所在表空间充足，但 DBA 报警“某索引段增长过快”，而业务写入量并无突增
COALESCE 和 REBUILD 的关键区别别搞混
这是最容易踩坑的地方。二者目标不同，锁行为、资源消耗、结果也完全不同：
-
COALESCE：仅合并相邻的、有空闲空间的叶块，不移动数据到新段，不改变索引物理位置，全程在线，只持SS（sub-share）锁，业务 DML 几乎无感 -
REBUILD：在新段中完整重写整个索引，释放全部旧段空间，需要额外空间（约等于原索引大小），持EXCLUSIVE表级锁（对全局索引）或分区级锁（对局部索引），且会导致依赖该索引的 SQL 游标失效 - 如果索引已严重碎片化（如
DEL_LF_ROWS占比超 40%），COALESCE效果有限，此时应选REBUILD ONLINE，而非硬撑 -
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，观察access_predicates和filter_predicates是否更稳定，consistent gets是否下降
真正难的是判断“到底该不该动”——监控 v$object_usage 看索引是否真被用；查 dba_hist_sqlstat 看关联 SQL 的逻辑读趋势；否则一顿 COALESCE 下去，可能只是给运维日志添了一行，对性能毫无改善。
