如何将表A数据插入表B并去重？现有存储过程处理百万行异常

• 表B用来存储无重复数据，表A存储包含重复数据的全量信息（因为依赖表B的系统故障重启时，没有验证待插入行是否存在的过滤机制，所以用表A做兜底存储）
• 核心需求：将表A中指定日期的数据同步到表B，但仅插入表B中不存在的行
• 现有存储过程处理百万级数据时性能极差，无法高效完成任务

原存储过程代码 #

CREATE PROCEDURE remove_emp (p_date IN Date) AS
   array_nonrepeated  clob;
   curr_num_transaction varchar2(50);
   BEGIN
        --Loop through each record in table A
        FOR loop_table_a IN (SELECT num_transaction FROM table_a where date_transaction = p_date) LOOP
            --Query to validate if curr value of loop exist in table b
            SELECT num_transaction INTO curr_num_transaction from table_b where num_transaction = loop_table_a.num_transaction;
            
            --If condition
            IF curr_num_transaction IS NULL
            THEN
                INSERT INTO table_b(num_transaction,date_transaction,total,user_insert)
                SELECT num_transaction,date_transaction,total,user_insert FROM table_a where num_transaction = curr_num_transaction;
            END IF;
        END LOOP;
   END;
/

• 逐行循环导致IO爆炸：对表A的每一行都单独发起一次表B查询，百万级数据会触发百万次独立查询，IO开销直接拉满
• 逻辑漏洞+冗余操作：
  1. 如果表B中无匹配行，SELECT ... INTO会直接抛出NO_DATA_FOUND异常，存储过程直接中断，根本无法完成全量数据处理
  2. 插入时又重复查询表A，平白增加额外IO消耗
• 索引缺失雪上加霜：如果table_a.date_transaction、table_b.num_transaction未建立索引，查询速度会进一步变慢

方案1：使用MERGE语句（首推） #

Oracle的MERGE语句可一次性完成匹配检查与插入操作，完全替代低效的逐行循环：

CREATE PROCEDURE sync_emp_to_b (p_date IN DATE) AS
BEGIN
    MERGE INTO table_b b
    USING (
        SELECT num_transaction, date_transaction, total, user_insert
        FROM table_a
        WHERE date_transaction = p_date
        -- 可选：先对表A内的重复行去重，减少无效匹配操作
        GROUP BY num_transaction, date_transaction, total, user_insert
    ) a
    ON (b.num_transaction = a.num_transaction)
    WHEN NOT MATCHED THEN
        INSERT (num_transaction, date_transaction, total, user_insert)
        VALUES (a.num_transaction, a.date_transaction, a.total, a.user_insert);
    COMMIT;
END;
/

方案2：使用INSERT ... NOT EXISTS #

如果仅需要插入无需更新，这种写法更简洁：

CREATE PROCEDURE sync_emp_to_b (p_date IN DATE) AS
BEGIN
    INSERT INTO table_b(num_transaction, date_transaction, total, user_insert)
    SELECT a.num_transaction, a.date_transaction, a.total, a.user_insert
    FROM table_a a
    WHERE a.date_transaction = p_date
      AND NOT EXISTS (
          SELECT 1 FROM table_b b
          WHERE b.num_transaction = a.num_transaction
      )
    -- 可选：对表A内的重复行去重
    GROUP BY a.num_transaction, a.date_transaction, a.total, a.user_insert;
    COMMIT;
END;
/

• 给table_a.date_transaction建立索引，加快指定日期数据的查询速度
• 给table_b.num_transaction建立唯一索引（建议直接设为主键或唯一约束，符合表B无重复数据的特性），大幅提升匹配检查效率
• 批量数据处理场景下，永远避免逐行循环逻辑，Oracle对批量DML的优化远优于单条循环操作

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