ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Update/ delete from a huge table with intermittent commits

Update/ delete from a huge table with intermittent commits

原创 Linux操作系统 作者:xhailiang 时间:2006-10-11 00:00:00 0 删除 编辑
rem -----------------------------------------------------------------------
rem Filename: plsloop.sql
rem Purpose: Example: UPDATE/DELETE in a loop and commit very X records
rem Handy for huge tables that cause rollback segment problems
rem DON'T ISSUE COMMIT TOO FREQUENTLY!
rem Date: 09-Apr-1999; Updated: 25-Nov-2004
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

rem -----------------------------------------------------------------------
rem Filename:  plsloop.sql
rem Purpose:   Example: UPDATE/DELETE in a loop and commit very X records
rem            Handy for huge tables that cause rollback segment problems
rem            DON'T ISSUE COMMIT TOO FREQUENTLY!
rem Date:      09-Apr-1999; Updated: 25-Nov-2004
rem Author:    Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

declare
  i number := 0;
  cursor s1 is SELECT rowid, t.* FROM tab1 t WHERE col1 = 'value1';
begin
  for c1 in s1 loop
      update tab1 set col1 = 'value2'
             where rowid = c1.rowid;

      i := i + 1;              -- Commit after every X records
      if i > 10000 then
         commit;
         i := 0;
      end if;

  end loop;
  commit;
end;
/

-- Note: More advanced users can use the mod() function to commit every N rows. 
--       No counter variable required:
--
-- if mod(i, 10000) 
--    commit;
--    dbms_output.put_line('Commit issued for rows up to: '||c1%rowcount);
--  end if;
--

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-171893/,如需转载,请注明出处,否则将追究法律责任。

下一篇: SU oracle user
请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    270
  • 访问量
    415468