针对百万级记录表的PL/SQL更新操作性能排查求助

阿华AIGC实验室

2026-4-28

Oracle批量更新性能优化问题求助

我现在遇到一个Oracle更新性能的问题：用小表t2的数据更新大表t1时，更新500条记录的comment code很顺畅，但更新超过1000条时居然要耗时30分钟。我试过用bulk collect的方式，也给comment code字段建了索引，但性能提升不明显。以下是我目前用的PL/SQL代码：

DECLARE
 lv_row_count NUMBER(9) := 0;
 lv_total_count NUMBER(9) := 0;
 lv_commit_cnt SIMPLE_INTEGER:=0;
-- 注意：原代码里lv_limit未定义，属于遗漏的变量声明
BEGIN
 FOR rec in (
 SELECT a.t1_id, a.t1_orig_code, t2_orig_code, a.t1_comment_code, t2_code, a.t1_restrict_update_ind, t2_restrict_update_ind, a.t1_data_origin, t2_data_origin, a.t1_purge_ind, t2_purge_ind, a.t1_created_date, a.rowid
 FROM t1 a
 JOIN t2 ON t2_code = a.t1_comment_code
 WHERE a.t1_comment_code in ('A','B','C','C1','D3')
 AND ( a.t1_orig_code != t2_orig_code OR a.t1_restrict_update_ind !=t2_restrict_update_ind OR a.t1_data_origin != t2_data_origin OR a.t1_purge_ind != t2_purge_ind)
 )
 LOOP
 lv_total_count := lv_total_count + 1;
 UPDATE t1
 SET t1_ORIG_CODE= rec.t2_orig_code,
 t1_RESTRICT_UPDATE_IND = 'Y', -- 原代码此处多了一个逗号，属于语法错误
 t1_DATA_ORIGIN = rec.t2_data_origin,
 t1_PURGE_IND =rec.t2_purge_ind
 WHERE t1.rowid =rec.rowid ;
 lv_commit_cnt:=lv_commit_cnt+1;
 IF MOD(lv_commit_cnt,lv_limit)=0 THEN -- lv_limit未定义，执行时会报错
 lv_commit_cnt:=0;
 COMMIT;
 END IF;
 dbms_output.put_line('a.t1_pidm -'||rec.t1_pidm || 'a.t1_orig_code -'||rec.t1_orig_code ||'Updated');
 END LOOP;
 COMMIT;
 dbms_output.put_line('Total_count- '||lv_total_count);
 -- dbms_output.put_line('No record');
 END;

优化建议：

替换游标逐行更新为批量更新（MERGE语句）：游标循环是行级操作，Oracle在处理大量数据时，单条基于集合的语句效率要高得多。你可以用MERGE来实现，示例如下：

MERGE INTO t1 target
USING (
    SELECT 
        t2.t2_orig_code,
        t2.t2_restrict_update_ind,
        t2.t2_data_origin,
        t2.t2_purge_ind,
        t1.rowid AS rid
    FROM t1
    JOIN t2 ON t1.t1_comment_code = t2.t2_code
    WHERE t1.t1_comment_code IN ('A','B','C','C1','D3')
    AND (
        t1.t1_orig_code != t2.t2_orig_code 
        OR t1.t1_restrict_update_ind != t2.t2_restrict_update_ind 
        OR t1.t1_data_origin != t2.t2_data_origin 
        OR t1.t1_purge_ind != t2.t2_purge_ind
    )
) source
ON (target.rowid = source.rid)
WHEN MATCHED THEN UPDATE SET
    target.t1_ORIG_CODE = source.t2_orig_code,
    target.t1_RESTRICT_UPDATE_IND = 'Y',
    target.t1_DATA_ORIGIN = source.t2_data_origin,
    target.t1_PURGE_IND = source.t2_purge_ind;
COMMIT;

这个语句会一次性处理所有符合条件的记录，避免逐行循环的额外开销。

修复代码中的语法错误：原代码里lv_limit变量没有声明，执行时会报错；另外SET子句里t1_RESTRICT_UPDATE_IND = 'Y'前面多了一个逗号，这些问题先修正才能保证代码正常运行。
移除dbms_output的逐行输出：每次循环都输出日志会极大拖慢性能，尤其是数据量较大时。如果需要日志，可以改成每N条记录输出一次，或者完全移除，改用后台日志表记录更新情况。
检查索引的合理性：
- 确保t2.t2_code（连接条件）有索引，这样JOIN操作会更快；
- t1.t1_comment_code的索引是有用的，但如果t1是大表，还要检查WHERE条件里的其他字段是否需要组合索引，比如(t1_comment_code, t1_orig_code, t1_restrict_update_ind)这样的组合索引，能让筛选更高效；
- 注意：更新操作会维护表上的所有索引，所以如果t1上有很多不必要的索引，会增加更新的耗时，可以考虑临时禁用非必要索引，更新完成后再重建。
更新表统计信息：如果表的统计信息过时，Oracle优化器可能会生成低效的执行计划。可以执行以下语句收集最新的统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 't1', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 't2', CASCADE => TRUE);