oracle 對錶中的記錄進行大批量刪除

selectshen發表於2015-01-27
Oracle
oracle 對錶中的記錄進行大批量刪除數量時,常常採用分批刪除,逐次提交.其目的大概有三個原因:
1.避免對其他事務select的影響
如果其他事務有需要查詢這些要刪除的記錄,就需要去undo段查詢前映像.分批逐次可以減少行更新的時間,
以減少這種情況的發生.
2.避免各事務dml的鎖等待
如果要刪除的這些記錄上,有其他事務在做dml操作,就可能會產生相互的行鎖等待.分批逐次可以減少行鎖定的
時間,以減少這種情況的發生.
3.減少使用臨時表空間對效能產生的影響
在關聯刪除時,可能會用到sort或hash區,一次對大量記錄進行操作,如果sort_area_size或hash_area_size大小不
夠就會使用臨時表空間,效能會降低.分批逐次可以減少單次操作的記錄數,以減少這種情況的發生.

以下是一些對大批量刪除進行分批刪除逐次提交的程式碼,可根據自己的實際情況測試修改後實施.

--對無關聯的單表中的記錄按條件刪除
declare
n_count number;
n_rownum number:=10000;
begin
select count(*) into n_count from tb_detail where createdate
for i in 1..ceil(icount/irownum) loop
    delete from  tb_detail a
    where  createdate and rownum<=n_rownum ;
    commit;
end loop;
end;

--對有關聯的表按條件刪除
declare
  type ridArray is table of rowid index by binary_integer;
  type dtArray is table of varchar2(50) index by binary_integer;
  v_rowid         ridArray;
  v_fid_to_delete dtArray;
  n_delete        number;
  n_rownum        number:=10000;
begin
  select count(*)
    into n_delete
    from tb_main
   where createdate < to_date('20140101', 'yyyymmdd');
  for i in 1 .. ceil(n_delete / n_rownum) loop
    select fid, rowid BULK COLLECT
      INTO v_fid_to_delete, v_rowid
      from tb_main
     where createdate < to_date('20140101', 'yyyymmdd')
       and rownum <= n_rownum;
    forall j in 1 .. v_fid_to_delete.COUNT
      delete from tb_detail where fid = v_fid_to_delete(j);
    forall k in 1 .. v_rowid.COUNT
      delete from tb_main where rowid = v_rowid(k);
    commit;
  end loop;
end;

--對有關聯的表按條件刪除子表或主表
declare
  type dtArray is table of varchar2(50) index by binary_integer;
  v_fid_to_delete dtArray;
  n_delete        number;
  n_rownum        number := 10000;
begin
  select fid BULK COLLECT
    INTO v_fid_to_delete
    from tb_main
   where createdate < to_date('20140601', 'yyyymmdd');
  for i in 1 .. ceil(v_fid_to_delete.COUNT / n_rownum) loop
    forall j in (i - 1) * n_rownum + 1 .. least(i * n_rownum,v_fid_to_delete.COUNT)
      delete from tb_detail where fid = v_fid_to_delete(j);
    commit;
  end loop;
end;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-1417272/，如需轉載，請註明出處，否則將追究法律責任。

相關文章