vage的delete大量資料方法

n-lauren發表於2013-01-06
如果業務無法停止的話,主要有這三種思路:
=======================================================================================================
思路1:根據ROWID分片、再利用Rowid排序、批量處理、回表刪除。
        在業務無法停止的時候,選擇這種方式,的確是最好的。一般可以控制在每一萬行以內提交一次,不會對回滾段造成太大壓力(我在做大DML時,通常選
擇一兩千行一提交)。選擇業務低峰時做,對應用也不至於有太大影響。
        感謝htyansp,在49樓提供了一個很簡捷的指令碼,如果你對這種方式不熟悉,可以參考此指令碼:
declare  
   cursor mycursor is SELECT  ROWID FROM TEST WHERE  XXX=XXXX  order by rowid;  
況來定。
   type rowid_table_type is  table  of rowid index by pls_integer;
   v_rowid   rowid_table_type;
BEGIN
   open mycursor;
   loop
     fetch   mycursor bulk collect into v_rowid  limit 5000;  
     exit when v_rowid.count=0;
     forall i in v_rowid.first..v_rowid.last
        delete from test  where rowid=v_rowid(i);
     commit;
   end loop;
   close mycursor;
END;
/
        這種方法的缺點是排序有可能會消耗太多臨時表空間。還有一種方式,先根據Rowid分片。將一個大表用Rowid劃分成多個部分,每部分單獨根據Rowid排
序。這種方式的另一個優點就是還可以並行。
        有一次我需要刪除DW庫一個大表中滿足條件的行。應用方保證不會再出現此條件的行,我只需要在幾天內,將所有滿足條件的行刪除完即可。此表所在
的表空間有幾十個資料檔案(每個檔案32G),我用如下的命令生成表在每個檔案中行的ROWID範圍:
select dbms_rowid.ROWID_CREATE(1,12227,file_id,MIN(BLOCK_ID),0),dbms_rowid.ROWID_CREATE(1,12227,file_id,MAX(BLOCK_ID+BLOCKS-1),8192) from 
dba_extents where segment_name='DML_TST' group by file_id order by file_id;
此命令中DATA_OID是dba_objects 中data_object_id列值。
        然後,根據上面得到的ROWID範圍操作目標表。其實就是將htyansp的儲存過程中第二行,根據生成的ROWID修改如下:
        cursor mycursor is SELECT  ROWID FROM TEST WHERE  rowid between 'ROWID' and  'ROWID' and XXX=XXXX order by rowid;
        
        儲存過程其他行基本不變。
        搞幾十個這樣的儲存過程,開幾個會話並行著跑。
        另外,TOM在9i&10G程式設計藝術 648頁到652頁有一個很好的例子,其中650頁自動生成ROWID部分,可以參考。
        使用這種方式最大的優點就是效能可控,需要快點的話,可以多設幾個並行。想慢點的,並行就少點。而且,一次處理的行數有限,對ROWID的排序不會
撐爆臨時表空間。
=======================================================================================================
思路二:根據ROWID分片、非批量處理、回表刪除
        比如,要刪除dml_tst中ID等於Value的行,最基本的儲存過程如下:
declare
        CURSOR test2_cs(value number,rid1 rowid,rid2 rowid) 
          IS SELECT id from dml_tst
          where id=value and rowid between rid1 and  rid2 
          FOR UPDATE ; 
        k number:=0;
BEGIN
   FOR c1_rec IN test2_cs(3338,'AAAC/DAAEAAAABJAAA','AAAC/DAAEAAAABQCAA') LOOP
            delete dml_tst where CURRENT OF test2_cs;
   END LOOP;
END;
/
        這種方式也可以根據ROWID分片,只會對錶進行一次掃描。但沒有批量處理,效能反而不如上面。
=======================================================================================================
思路三: ON PREBUILT物化檢視方法

這種方式,阿里遷移資料的確使用較多,也是一種不錯的方式。速度沒有方法一快,但比較簡單,而且對業務基本上沒有影響。另外,對於刪除操作,可以釋放
刪除過的空間。缺點就是需要有主鍵。
假設目標表是P3,主鍵列是ID1,要刪除ID2列於小1000的行:
步1,建立中間表p3_m:
create table p3_m as select * from p3 where 0=1;
步2,建產和中間表同名的物化檢視,一定要有ON PREBUILT選項:
CREATE MATERIALIZED VIEW p3_m
ON PREBUILT TABLE AS
select * from p3 where id2>=1000;  
步3:新增物化檢視日誌:
CREATE MATERIALIZED VIEW LOG ON p3 WITH PRIMARY KEY,sequence (id2,id3,cc1,cc2) INCLUDING NEW VALUES;
步4:在資料庫空閒的時候,進行一次完全重新整理:
exec dbms_mview.refresh('P3_M','C');
完全重新整理後,可以在中間表上建立和目標表一樣的索引、約束等等
步5:進行個一、兩次增量重新整理:
exec dbms_mview.refresh('P3_M','F');
步6:將原表鎖住,最後進行一次增量重新整理,然後馬上Rename目標表為其他名字
lock table p3 in EXCLUSIVE mode;
exec dbms_mview.refresh('P3_M','F');
drop MATERIALIZED VIEW LOG ON p3;
alter table p3 rename to p3_n;
步7:刪除物化檢視,修改中間表為原目標表的名字:
drop MATERIALIZED VIEW p3_m;
alter table p3_m rename to p3;
步8:確定原表如果沒有用了,可以刪除改過名的原表
也可以使用再線重定義,思路和這個類似。
======================================================================================
        如果不影響應用的話,常規方法也就這些了。這三種思路,也可以用於Update。
        根據這三種思路,我們可以結合自身應用情況加以改變。總能找到一款適合應用的方法。
        注意事項是
        1、注意備份
        2、千萬注意不要太猛,曾經有一次同事因為Update的太猛,影響了我們一個重要的前臺應用。一定要注意,一次提交的行數不能太高。

        如果應用可以停,哪方法就太多了。
1、CTAS的方法建立一個新表,排除要DELETE的資料,再改名。為提高速度,還可以禁用索引,DML完再重建。
2、只匯出不刪除的資料,再匯入,再改名
3、如果表空間可以設為只讀,還有壇友的表空間遷移,遷到測試平臺,慢慢刪除再匯入回來的方法
等等。可以停應用的方法就很多了。

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

相關文章