什么情况下，oracle表和索引产生大量碎片？特别一些中间表频繁insert，delete，删除后空间不是重用吗，为什么会产生大量碎片，如何解决这些问题？

Oracle表和索引产生大量碎片的主要原因及解决方案如下：

一、碎片产生的核心原理

1. 表碎片产生原因

• 频繁DML操作 ：

  • INSERT ：当新数据无法填充已释放的块（如行长度不同）时，会分配新区块。

  • DELETE ：删除操作会释放块中的行空间，但这些空间可能因 PCTFREE 参数限制无法被重用。

  • UPDATE ：可能导致行迁移（Row Migration）或行链接（Row Chaining），加剧块内碎片。

• 高水位线（HWM）问题 ：

  • HWM是表段中已分配空间的最高位置。删除数据后HWM不会自动下降，全表扫描仍需读取HWM以下所有块。

  • 即使表中大部分数据被删除，HWM以下的空闲空间只能被当前对象重用，无法释放到表空间供其他对象使用。

• 存储参数不合理 ：

  • PCTFREE 设置过低：块内预留空间不足，导致行迁移。

  • PCTUSED 设置过高（传统空闲列表管理）：块频繁进入/退出空闲列表，增加碎片风险。

2. 索引碎片产生原因

• B树索引特性 ：

  • 删除操作 ：导致叶子节点出现空闲空间，但索引键值不连续时，这些空间无法被新插入的数据利用。

  • 插入操作 ：可能导致叶子节点分裂（Leaf Split），形成不连续的物理存储。

二、为何删除后空间未被有效重用？

• 块级碎片 ：

  • 块内空间未被填满（如 PCTFREE 设置过高），新插入的数据行无法匹配块内剩余空间。

• 区段（Extent）分配策略 ：

  • 如果表使用 UNIFORM SIZE 区段分配，删除操作释放的区段可能无法被其他对象使用。

• 自动段空间管理（ASSM）限制 ：

  • ASSM使用位图管理块，但频繁删除可能导致块标记为“部分空闲”，而新插入可能优先选择新块。

三、解决方案

1. 表碎片治理

• 重组表 ：

  sql

  -- 移动表并重置HWMALTER TABLE table_name MOVE TABLESPACE target_tablespace;-- 重建表并保留统计信息（Oracle 12c+）ALTER TABLE table_name MOVE ONLINE INCLUDING ROWS;

  • 优点 ：直接释放HWM以下空间。

  • 缺点 ：需要维护窗口，表锁可能影响业务。

• 在线段收缩 （需开启行移动）：

  sql

  ALTER TABLE table_name ENABLE ROW MOVEMENT;ALTER TABLE table_name SHRINK SPACE COMPACT;ALTER TABLE table_name SHRINK SPACE;

  • 适用场景 ：对业务影响小，适合轻度碎片。

• 调整存储参数 ：

  sql

  ALTER TABLE table_name PCTFREE 10 PCTUSED 40;

  • 建议 ：结合业务负载调整 PCTFREE/PCTUSED ，避免行迁移和空间浪费。

• 分区表 ：

  • 按时间或范围分区，定期 TRUNCATE 旧分区，避免全表删除。

2. 索引碎片治理

• 索引重建 ：

  sql

  ALTER INDEX index_name REBUILD TABLESPACE target_tablespace;

  • 注意 ：在线重建（ REBUILD ONLINE ）可减少锁争用。

• 索引合并 （COALESCE）：

  sql

  ALTER INDEX index_name COALESCE;

  • 适用场景 ：合并相邻空闲空间，不释放区段。

3. 全局优化策略

• 使用本地管理的表空间（LMT） ：

  • 避免字典管理表空间（DMT）的区段分配效率问题。

• 定期监控与分析 ：

  • 查询 DBA_TABLES.AVG_ROW_LEN 、 DBA_TABLES.BLOCKS 等视图，评估碎片程度。

  • 使用 DBMS_SPACE.SPACE_USAGE 分析块空间分布。

• 自动化任务 ：

  • 通过Oracle Scheduler定期执行碎片整理任务。

四、最佳实践

1. 中间表设计 ：

   • 使用 NOLOGGING 和 APPEND 提示加速插入。

   • 选择全局临时表（GTT）或内存表（In-Memory）减少物理I/O。

2. 分区策略 ：

   • 按业务周期分区，通过 DROP PARTITION 快速清理数据。

3. 空间预分配 ：

   • 预先分配足够区段（ ALLOCATE EXTENT ），减少动态扩展带来的碎片。

五、总结

Oracle碎片产生的本质是 空间分配与数据分布的不匹配 。通过合理的存储参数设计、定期维护（如重组、重建）、结合分区技术，可有效减少碎片对性能的影响。对于关键业务表，建议在低峰期通过 MOVE 或 SHRINK 操作维护，同时监控 DBA_SEGMENTS.BYTES 和 DBA_SEGMENTS.BLOCKS 的变化趋势。