优化Oracle过程高效处理大型表

cgh8pdjw  于 2023-10-16  发布在  Oracle
关注(0)|答案(2)|浏览(92)

我正在处理一个Oracle存储过程,它循环遍历表的每一条记录&执行计算&将计算结果保存在“结果”字段中的同一条记录上。这个表大约有1500万到2000万条记录。
我需要一种方法来跟踪记录的处理状态、剩余记录的数量,并实现增量提交,以便在失败的情况下从最后一个提交点恢复处理。
目前,我不知道有多少记录被处理,也不知道这个过程是否会完成。此外,如果有任何未处理的异常发生,它不应该从记录#1开始处理。

ki0zmccv

ki0zmccv1#

如果可能,切换到面向集合的处理,即更新整个表 * 一次 *。但这可能会导致问题(提示:回滚段大小),因为涉及数百万行。此外,如果处理很复杂,那么这可能甚至不适合单个update语句。
如果它必须是逐行的,那么为了更容易的日志记录目的,创建一个自治事务过程,它将记录已经被处理到另一个表中的行。这样做,你可以随时查询它,看看已经做了什么。不要依赖DBMS_OUTPUT,因为它不会 * 打印 * 任何东西,直到整个过程结束,然后-太晚了,因为你已经知道结果了。
将内部BEGIN-EXCEPTION-END块包含到循环中。异常处理部分应该记录错误(到适当的表中),不会引发错误,但让下一个循环继续处理。
在提交时,创建一个在循环中递增的局部变量;检查它的值,一旦它达到例如。100.000提交并重置它。在过程结束时也提交。
简化:

declare
  i number := 0;
begin
  for cur_r in (select id from some_table) loop
    begin
      update target table set ...
      i := i + 1;
      if i = 100000 then
         commit;
         i := 0;
      end if;
      p_log(cur_r.id);             -- mark this ID as "processed"

    exception
      when others then
        p_err(cur_r.id, sqlerrm);  -- store ID and error message
    end;
  end loop;
  commit;
end;
1cklez4t

1cklez4t2#

如果您必须“更新”表中的每一行,那么最好使用计算数据重新创建表。

insert into new_tab
select old_tab_columns..., <results_calc>
from old_tab

然后,您可以利用并行处理、直接路径加载、日志记录等

相关问题