久久国产成人av_抖音国产毛片_a片网站免费观看_A片无码播放手机在线观看,色五月在线观看,亚洲精品m在线观看,女人自慰的免费网址,悠悠在线观看精品视频,一级日本片免费的,亚洲精品久,国产精品成人久久久久久久

搜索
分享

ORACLE大表中刪除部分?jǐn)?shù)據(jù)最佳方案

 紫衣風(fēng)華 2015-03-10

如果業(yè)務(wù)無法停止的話,，主要有這三種思路：
=======================================================================================================
思路1：根據(jù)ROWID分片,、再利用Rowid排序、批量處理,、回表刪除,。
        在業(yè)務(wù)無法停止的時(shí)候，選擇這種方式,，的確是最好的,。一般可以控制在每一萬行以內(nèi)提交一次，不會(huì)對回滾段造成太大壓力（我在做大DML時(shí),，通常選
擇一兩千行一提交）,。選擇業(yè)務(wù)低峰時(shí)做，對應(yīng)用也不至于有太大影響,。
        感謝htyansp,，在49樓提供了一個(gè)很簡捷的腳本，如果你對這種方式不熟悉,，可以參考此腳本：
declare  
   cursor mycursor is SELECT  ROWID FROM TEST WHERE  XXX=XXXX  order by rowid;   <--------按ROWID排序的Cursor,，刪除條件是XXX=XXXX，根據(jù)實(shí)際情
況來定,。
   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;   <--------每次處理5000行，也就是每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;
/
        這種方法的缺點(diǎn)是排序有可能會(huì)消耗太多臨時(shí)表空間。還有一種方式,，先根據(jù)Rowid分片,。將一個(gè)大表用Rowid劃分成多個(gè)部分，每部分單獨(dú)根據(jù)Rowid排
序,。這種方式的另一個(gè)優(yōu)點(diǎn)就是還可以并行,。
        有一次我需要?jiǎng)h除DW庫一個(gè)大表中滿足條件的行。應(yīng)用方保證不會(huì)再出現(xiàn)此條件的行,，我只需要在幾天內(nèi),，將所有滿足條件的行刪除完即可。此表所在
的表空間有幾十個(gè)數(shù)據(jù)文件（每個(gè)文件32G）,，我用如下的命令生成表在每個(gè)文件中行的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列值,。
        然后，根據(jù)上面得到的ROWID范圍操作目標(biāo)表,。其實(shí)就是將htyansp的存儲(chǔ)過程中第二行,，根據(jù)生成的ROWID修改如下：
        cursor mycursor is SELECT  ROWID FROM TEST WHERE  rowid between 'ROWID' and  'ROWID' and XXX=XXXX order by rowid;
        
        存儲(chǔ)過程其他行基本不變。
        搞幾十個(gè)這樣的存儲(chǔ)過程,，開幾個(gè)會(huì)話并行著跑,。
        另外，TOM在9i&10G編程藝術(shù) 648頁到652頁有一個(gè)很好的例子,，其中650頁自動(dòng)生成ROWID部分,，可以參考。
        使用這種方式最大的優(yōu)點(diǎn)就是性能可控,，需要快點(diǎn)的話,，可以多設(shè)幾個(gè)并行。想慢點(diǎn)的,，并行就少點(diǎn),。而且，一次處理的行數(shù)有限,，對ROWID的排序不會(huì)
撐爆臨時(shí)表空間,。

案例參考：http://www./archives/%e5%88%a9%e7%94%a8rowid%e5%88%86%e5%9d%97%e5%ae%9e%e7%8e%b0%e9%9d%9e%e5%88%86%e5%8c%ba%e8%a1%a8%e7%9a%84%e5%b9%b6%e8%a1%8cupdate%e4%b8%8edelete.html

=======================================================================================================
思路二：根據(jù)ROWID分片、非批量處理,、回表刪除
        比如,，要?jiǎng)h除dml_tst中ID等于Value的行，最基本的存儲(chǔ)過程如下：
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;
/
        這種方式也可以根據(jù)ROWID分片,，只會(huì)對表進(jìn)行一次掃描,。但沒有批量處理，性能反而不如上面,。
=======================================================================================================
思路三： ON PREBUILT物化視圖方法

這種方式,，阿里遷移數(shù)據(jù)的確使用較多，也是一種不錯(cuò)的方式。速度沒有方法一快,，但比較簡單,，而且對業(yè)務(wù)基本上沒有影響。另外,，對于刪除操作,，可以釋放
刪除過的空間。缺點(diǎn)就是需要有主鍵,。
假設(shè)目標(biāo)表是P3,，主鍵列是ID1，要?jiǎng)h除ID2列于小1000的行:
步1,，建立中間表p3_m：
create table p3_m as select * from p3 where 0=1;
步2,，建產(chǎn)和中間表同名的物化視圖，一定要有ON PREBUILT選項(xiàng)：
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：在數(shù)據(jù)庫空閑的時(shí)候,，進(jìn)行一次完全刷新：
exec dbms_mview.refresh('P3_M','C');
完全刷新后,，可以在中間表上創(chuàng)建和目標(biāo)表一樣的索引、約束等等
步5：進(jìn)行個(gè)一,、兩次增量刷新：
exec dbms_mview.refresh('P3_M','F');
步6：將原表鎖住,，最后進(jìn)行一次增量刷新，然后馬上Rename目標(biāo)表為其他名字
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：刪除物化視圖,，修改中間表為原目標(biāo)表的名字：
drop MATERIALIZED VIEW p3_m;
alter table p3_m rename to p3;
步8：確定原表如果沒有用了,，可以刪除改過名的原表
也可以使用再線重定義，思路和這個(gè)類似,。

在線重定義案件：http://www./archives/%e5%88%a9%e7%94%a8oracle%e5%9c%a8%e7%ba%bf%e9%87%8d%e5%ae%9a%e4%b9%89online-redefinition%e6%b8%85%e7%90%86%e5%8e%86%e5%8f%b2%e6%95%b0%e6%8d%ae.html
======================================================================================
        如果不影響應(yīng)用的話,，常規(guī)方法也就這些了。這三種思路,，也可以用于Update,。
        根據(jù)這三種思路，我們可以結(jié)合自身應(yīng)用情況加以改變,?？偰苷业揭豢钸m合應(yīng)用的方法。
        注意事項(xiàng)是
        1,、注意備份
        2,、千萬注意不要太猛，曾經(jīng)有一次同事因?yàn)閁pdate的太猛,，影響了我們一個(gè)重要的前臺(tái)應(yīng)用,。一定要注意，一次提交的行數(shù)不能太高,。

        如果應(yīng)用可以停,，哪方法就太多了,。
1、CTAS的方法創(chuàng)建一個(gè)新表,，排除要DELETE的數(shù)據(jù),，再改名,。為提高速度,，還可以禁用索引，DML完再重建,。
2,、只導(dǎo)出不刪除的數(shù)據(jù)，再導(dǎo)入,，再改名
3,、如果表空間可以設(shè)為只讀，還有壇友的表空間遷移,，遷到測試平臺(tái),，慢慢刪除再導(dǎo)入回來的方法
等等?？梢酝?yīng)用的方法就很多了,。

如果業(yè)務(wù)無法停止的話，主要有這三種思路：
=======================================================================================================
思路1：根據(jù)ROWID分片,、再利用Rowid排序,、批量處理、回表刪除,。
        在業(yè)務(wù)無法停止的時(shí)候,，選擇這種方式，的確是最好的,。一般可以控制在每一萬行以內(nèi)提交一次,，不會(huì)對回滾段造成太大壓力（我在做大DML時(shí)，通常選
擇一兩千行一提交）,。選擇業(yè)務(wù)低峰時(shí)做,，對應(yīng)用也不至于有太大影響。
        感謝htyansp,，在49樓提供了一個(gè)很簡捷的腳本,，如果你對這種方式不熟悉，可以參考此腳本：
declare  
   cursor mycursor is SELECT  ROWID FROM TEST WHERE  XXX=XXXX  order by rowid;   <--------按ROWID排序的Cursor,，刪除條件是XXX=XXXX,，根據(jù)實(shí)際情
況來定。
   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;   <--------每次處理5000行,，也就是每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;
/
        這種方法的缺點(diǎn)是排序有可能會(huì)消耗太多臨時(shí)表空間,。還有一種方式,，先根據(jù)Rowid分片。將一個(gè)大表用Rowid劃分成多個(gè)部分,，每部分單獨(dú)根據(jù)Rowid排
序,。這種方式的另一個(gè)優(yōu)點(diǎn)就是還可以并行。
        有一次我需要?jiǎng)h除DW庫一個(gè)大表中滿足條件的行,。應(yīng)用方保證不會(huì)再出現(xiàn)此條件的行,，我只需要在幾天內(nèi)，將所有滿足條件的行刪除完即可,。此表所在
的表空間有幾十個(gè)數(shù)據(jù)文件（每個(gè)文件32G）,，我用如下的命令生成表在每個(gè)文件中行的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列值。
        然后,，根據(jù)上面得到的ROWID范圍操作目標(biāo)表,。其實(shí)就是將htyansp的存儲(chǔ)過程中第二行，根據(jù)生成的ROWID修改如下：
        cursor mycursor is SELECT  ROWID FROM TEST WHERE  rowid between 'ROWID' and  'ROWID' and XXX=XXXX order by rowid;
        
        存儲(chǔ)過程其他行基本不變,。
        搞幾十個(gè)這樣的存儲(chǔ)過程,，開幾個(gè)會(huì)話并行著跑。
        另外,，TOM在9i&10G編程藝術(shù) 648頁到652頁有一個(gè)很好的例子,，其中650頁自動(dòng)生成ROWID部分，可以參考,。
        使用這種方式最大的優(yōu)點(diǎn)就是性能可控,，需要快點(diǎn)的話，可以多設(shè)幾個(gè)并行,。想慢點(diǎn)的,，并行就少點(diǎn)。而且,，一次處理的行數(shù)有限,，對ROWID的排序不會(huì)
撐爆臨時(shí)表空間。

案例參考：http://www./archives/%e5%88%a9%e7%94%a8rowid%e5%88%86%e5%9d%97%e5%ae%9e%e7%8e%b0%e9%9d%9e%e5%88%86%e5%8c%ba%e8%a1%a8%e7%9a%84%e5%b9%b6%e8%a1%8cupdate%e4%b8%8edelete.html

=======================================================================================================
思路二：根據(jù)ROWID分片,、非批量處理,、回表刪除
        比如，要?jiǎng)h除dml_tst中ID等于Value的行,，最基本的存儲(chǔ)過程如下：
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;
/
        這種方式也可以根據(jù)ROWID分片,，只會(huì)對表進(jìn)行一次掃描。但沒有批量處理,，性能反而不如上面,。
=======================================================================================================
思路三： ON PREBUILT物化視圖方法

這種方式，阿里遷移數(shù)據(jù)的確使用較多,，也是一種不錯(cuò)的方式,。速度沒有方法一快,，但比較簡單，而且對業(yè)務(wù)基本上沒有影響,。另外,，對于刪除操作，可以釋放
刪除過的空間,。缺點(diǎn)就是需要有主鍵,。
假設(shè)目標(biāo)表是P3，主鍵列是ID1,，要?jiǎng)h除ID2列于小1000的行:
步1,，建立中間表p3_m：
create table p3_m as select * from p3 where 0=1;
步2，建產(chǎn)和中間表同名的物化視圖,，一定要有ON PREBUILT選項(xiàng)：
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：在數(shù)據(jù)庫空閑的時(shí)候，進(jìn)行一次完全刷新：
exec dbms_mview.refresh('P3_M','C');
完全刷新后,，可以在中間表上創(chuàng)建和目標(biāo)表一樣的索引,、約束等等
步5：進(jìn)行個(gè)一、兩次增量刷新：
exec dbms_mview.refresh('P3_M','F');
步6：將原表鎖住,，最后進(jìn)行一次增量刷新,，然后馬上Rename目標(biāo)表為其他名字
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：刪除物化視圖，修改中間表為原目標(biāo)表的名字：
drop MATERIALIZED VIEW p3_m;
alter table p3_m rename to p3;
步8：確定原表如果沒有用了,，可以刪除改過名的原表
也可以使用再線重定義,，思路和這個(gè)類似。

在線重定義案件：http://www./archives/%e5%88%a9%e7%94%a8oracle%e5%9c%a8%e7%ba%bf%e9%87%8d%e5%ae%9a%e4%b9%89online-redefinition%e6%b8%85%e7%90%86%e5%8e%86%e5%8f%b2%e6%95%b0%e6%8d%ae.html
======================================================================================
        如果不影響應(yīng)用的話,，常規(guī)方法也就這些了,。這三種思路，也可以用于Update,。
        根據(jù)這三種思路,，我們可以結(jié)合自身應(yīng)用情況加以改變?？偰苷业揭豢钸m合應(yīng)用的方法,。
        注意事項(xiàng)是
        1、注意備份
        2,、千萬注意不要太猛,，曾經(jīng)有一次同事因?yàn)閁pdate的太猛，影響了我們一個(gè)重要的前臺(tái)應(yīng)用,。一定要注意,，一次提交的行數(shù)不能太高。

        如果應(yīng)用可以停,，哪方法就太多了,。
1,、CTAS的方法創(chuàng)建一個(gè)新表，排除要DELETE的數(shù)據(jù),，再改名,。為提高速度，還可以禁用索引,，DML完再重建,。
2、只導(dǎo)出不刪除的數(shù)據(jù),，再導(dǎo)入,，再改名
3、如果表空間可以設(shè)為只讀,，還有壇友的表空間遷移,，遷到測試平臺(tái)，慢慢刪除再導(dǎo)入回來的方法
等等,?？梢酝?yīng)用的方法就很多了。

    本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間,，所有內(nèi)容均由用戶發(fā)布,，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式,、誘導(dǎo)購買等信息,，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,，請點(diǎn)擊一鍵舉報(bào),。
    轉(zhuǎn)藏 分享 獻(xiàn)花（0）

    0條評論

    發(fā)表

    請遵守用戶 評論公約

    類似文章 更多