Oracle（Toad环境）中INSERT INTO插入查询结果过慢问题求助

1. 优先使用CREATE TABLE AS SELECT（CTAS） #

如果目标空表不需要保留原有约束、触发器或索引（或可后续补充），CTAS是Oracle批量导入数据最快的方式之一：

CREATE TABLE target_table AS
SELECT ... -- 你的原查询语句
FROM ...;

创建完成后，再手动添加所需的主键、外键、索引和触发器即可。

2. 给INSERT语句添加APPEND提示 #

通过/*+ APPEND */提示让Oracle直接在表的高水位线之上写入数据，规避常规插入的空间管理开销：

INSERT /*+ APPEND */ INTO target_table
SELECT ... -- 你的原查询语句
FROM ...;
COMMIT;

注意：APPEND模式会对表施加独占锁，适合无并发写入的场景；必须显式提交才能让数据可见。

3. 临时禁用约束和触发器 #

若目标表存在主键、外键、唯一约束或触发器，插入时Oracle会逐行校验这些对象，大幅拖慢速度。可先禁用，完成插入后再启用：

-- 禁用所有约束
ALTER TABLE target_table DISABLE ALL CONSTRAINTS;
-- 禁用所有触发器
ALTER TABLE target_table DISABLE ALL TRIGGERS;

-- 执行INSERT操作
INSERT INTO target_table SELECT ... FROM ...;
COMMIT;

-- 重新启用约束和触发器
ALTER TABLE target_table ENABLE ALL CONSTRAINTS;
ALTER TABLE target_table ENABLE ALL TRIGGERS;

4. 用批量提交的PL/SQL块优化 #

通过BULK COLLECT和FORALL减少上下文切换，效率远高于常规逐行插入：

DECLARE
  CURSOR c_source_data IS
    SELECT ... -- 你的原查询语句
    FROM ...;
  TYPE t_data_batch IS TABLE OF c_source_data%ROWTYPE;
  v_data_batch t_data_batch;
BEGIN
  OPEN c_source_data;
  LOOP
    -- 每次批量获取10000行（可根据服务器性能调整）
    FETCH c_source_data BULK COLLECT INTO v_data_batch LIMIT 10000;
    EXIT WHEN v_data_batch.COUNT = 0;
    
    -- 批量插入
    FORALL i IN 1..v_data_batch.COUNT
      INSERT INTO target_table VALUES v_data_batch(i);
    COMMIT;
  END LOOP;
  CLOSE c_source_data;
END;
/

5. 临时调整表的日志和存储参数 #

（1）启用NOLOGGING减少日志生成 #

临时将目标表设为NOLOGGING模式，避免插入时生成大量redo日志：

ALTER TABLE target_table NOLOGGING;

-- 配合APPEND提示使用
INSERT /*+ APPEND */ INTO target_table SELECT ... FROM ...;
COMMIT;

-- 恢复为LOGGING模式（确保数据可恢复）
ALTER TABLE target_table LOGGING;

注意：NOLOGGING模式下，若发生数据库故障，该部分数据无法通过redo日志恢复，需确保已有备份。

（2）降低PCTFREE值 #

若表的PCTFREE设置过高（默认10），每个数据块会预留过多空间，导致插入时需要更多块操作：

ALTER TABLE target_table PCTFREE 1;

-- 执行INSERT操作
INSERT INTO target_table SELECT ... FROM ...;
COMMIT;

-- 恢复原PCTFREE值（比如10）
ALTER TABLE target_table PCTFREE 10;

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