CTAS重建Oracle大表后FORALL删除变慢，执行计划未变该如何解决？

1. 重新收集精准的统计信息 #

CTAS默认生成的统计信息多为估算值，即便手动收集过，也可能因采样率不足导致执行计划判断偏差。执行以下命令强制收集全量统计信息：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => '你的用户名',
    TABNAME => '新表名',
    ESTIMATE_PERCENT => 100, -- 全量采样确保统计信息精准
    CASCADE => TRUE, -- 同步收集关联索引的统计信息
    METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

2. 检查并修复索引碎片 #

CTAS后创建的索引（尤其是并行创建的索引）容易产生碎片，碎片会增加索引扫描时的IO开销。先验证索引结构：

ANALYZE INDEX 主键索引名 VALIDATE STRUCTURE;

再查询索引碎片率：

SELECT name, height, del_lf_rows / lf_rows AS frag_ratio
FROM INDEX_STATS
WHERE name = '主键索引名';

若碎片率超过20%，可选择轻量合并索引：

ALTER INDEX 主键索引名 COALESCE;

或彻底重建索引：

ALTER INDEX 主键索引名 REBUILD;

3. 对齐原表与新表的存储参数 #

CTAS会继承表空间的默认存储参数，可能与原表不一致（比如PCTFREE设置不合理会引发行迁移或空间浪费）。对比两者参数：

SELECT table_name, pct_free, pct_used, tablespace_name
FROM user_tables
WHERE table_name IN ('原表名', '新表名');

若新表PCTFREE与原表差异较大，调整参数：

ALTER TABLE 新表名 PCTFREE 原表的PCTFREE值;

4. 优化FORALL执行逻辑 #

• 拆分绑定数组：如果数组元素过多（比如超10000条），拆分成分批处理，避免内存溢出拖慢性能。示例：

DECLARE
  TYPE pk_tab IS TABLE OF 主键类型 INDEX BY PLS_INTEGER;
  v_pks pk_tab;
  CURSOR c_pks IS SELECT pk FROM 待删除主键表;
BEGIN
  OPEN c_pks;
  LOOP
    FETCH c_pks BULK COLLECT INTO v_pks LIMIT 1000; -- 分批获取数据
    EXIT WHEN v_pks.COUNT = 0;
    FORALL i IN 1..v_pks.COUNT
      DELETE FROM 新表名 WHERE pk = v_pks(i);
    COMMIT; -- 分批提交，降低Undo压力
  END LOOP;
  CLOSE c_pks;
END;
/

• 精简过滤条件：确保FORALL语句仅通过主键过滤，无多余条件或隐式列转换。

5. 检查外键关联的子表索引 #

删除父表数据时，Oracle会验证子表是否存在关联记录。若子表的外键列未创建索引，会触发子表全表扫描，严重拖慢删除速度。检查子表外键索引：

SELECT uc.table_name 子表名, uc.column_name 外键列, ui.index_name 索引名
FROM user_constraints uc
LEFT JOIN user_ind_columns ui ON uc.table_name = ui.table_name AND uc.column_name = ui.column_name
WHERE uc.r_constraint_name = (SELECT constraint_name FROM user_constraints WHERE table_name = '新表名' AND constraint_type = 'P');

若缺少对应索引，立即创建：

CREATE INDEX 子表外键索引名 ON 子表名(外键列);

6. 验证执行计划的实际IO消耗 #

用SQL_TRACE查看执行时的IO情况，对比原表删除时的逻辑读、物理读：

ALTER SESSION SET SQL_TRACE = TRUE;
-- 执行你的FORALL删除代码
ALTER SESSION SET SQL_TRACE = FALSE;

用TKPROF分析跟踪文件，若新表删除时的cr（逻辑读）、pr（物理读）远高于原表，说明数据块分布分散，可重组表：

ALTER TABLE 新表名 MOVE;
-- 重组后需重建索引
ALTER INDEX 主键索引名 REBUILD;

内容的提问来源于stack exchange，提问作者user2595886