Toad中生成缺失数字的SQL脚本执行超2小时，求优化建议

原脚本的性能瓶颈核心在于逐行循环+单次查询+单行插入的模式——每处理一条数据都要在PL/SQL引擎和SQL引擎间来回切换，重复查询table2的开销更是雪上加霜。以下是针对性的优化方案：

核心优化思路 #

放弃嵌套循环的逐行处理逻辑，改用批量生成数据+批量过滤+批量插入的方式，最大化利用Oracle SQL引擎的批量处理能力，减少不必要的上下文切换。

方案1：纯SQL生成范围并批量插入（首推） #

利用Oracle的CONNECT BY语法直接生成temp_bound中每个区间的所有数字，通过左连接过滤掉table2中已存在的数据，最后一次性插入table1。这种方式完全规避PL/SQL循环，性能提升最显著。

优化后的代码：

INSERT /*+ APPEND PARALLEL */ INTO table1 (col1, brnch_cd)
SELECT 
    tb.lower_b + level, 
    tb.brnch_Cd
FROM temp_bound tb
CONNECT BY LEVEL <= tb.uper_b - tb.lower_b
LEFT JOIN table2 t2 ON t2.col2 = tb.lower_b + level
WHERE t2.col2 IS NULL
GROUP BY tb.lower_b + level, tb.brnch_Cd; -- 若temp_bound存在重复区间，此句可避免生成重复数据
COMMIT;

方案2：PL/SQL集合批量处理 #

如果业务场景必须保留PL/SQL逻辑，可先将所有待生成数字存入集合，再批量过滤后插入：

DECLARE
    TYPE num_branch_rec IS RECORD (
        num_val NUMBER,
        brnch_cd VARCHAR2(50) -- 根据实际字段类型调整
    );
    TYPE num_branch_tab IS TABLE OF num_branch_rec;
    v_data num_branch_tab;
BEGIN
    -- 批量生成所有待插入的数字与分支码
    SELECT tb.lower_b + level, tb.brnch_Cd
    BULK COLLECT INTO v_data
    FROM temp_bound tb
    CONNECT BY LEVEL <= tb.uper_b - tb.lower_b;

    -- 批量插入过滤后的数据
    FORALL idx IN 1..v_data.COUNT
        INSERT INTO table1 (col1, brnch_cd)
        VALUES (v_data(idx).num_val, v_data(idx).brnch_cd)
        WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE col2 = v_data(idx).num_val);
    COMMIT;
END;
/

额外优化细节 #

• 给table2.col2创建索引：执行CREATE INDEX idx_table2_col2 ON table2(col2);，能大幅加速存在性检查的查询速度
• 合理使用APPEND提示：若table1是堆表，/*+ APPEND */可直接在数据文件末尾追加数据，减少日志写入开销（注意：此提示会绕过缓冲区，仅适合批量插入场景）
• Parallel提示的正确用法：Parallel提示仅在批量操作时有效，单行插入时添加无意义，建议在INSERT语句级别配置

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