vage的delete大量資料方法

n-lauren發表於2013-01-06
delete
如果業務無法停止的話，主要有這三種思路：
=======================================================================================================
思路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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章