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

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<to_date('20140101','yyyymmdd');
 for i in 1..ceil(icount/irownum) loop
    delete from  tb_detail a
    where  createdate<to_date('20140101','yyyymmdd') 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;