在使用delete語句刪除數(shù)據(jù)時(shí)，數(shù)據(jù)庫是要做日志記錄的，以便將來可以恢復(fù)數(shù)據(jù)，可是我在刪除上百萬條數(shù)據(jù)時(shí)，十分緩慢甚至死機(jī)，請問有沒有什么好方法? 網(wǎng)友觀點(diǎn)一:create or replace procedure delete_tableisi number(10);begin for x in (select * from emp where DEPTNO like 'a%') loop delete emp where emp.id = x.id i:=i+1; if i>1000 then commit; i:=0; end if; end loop;exception when others then dbms_out.put_line(sqlcode); rollback;end delete_table;網(wǎng)友觀點(diǎn)二:這個(gè)是我平常用來批量刪除數(shù)據(jù)，每500條數(shù)據(jù)提交一次。DECLARECNT NUMBER(10):=0;I NUMBER(10);BEGINSELECT COUNT(*) INTO CNT FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,'MM')='01';FOR I IN 1..TRUNC(CNT/500)+1 LOOPDELETE FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,'MM')='01' AND ROWNUM<=500;COMMIT;END LOOP;END;專家意見:幾個(gè)辦法:1. 假如刪除的數(shù)據(jù)是大部分,建議使用樓上的方法把要保留的數(shù)據(jù)放在一個(gè)臨時(shí)表里,truncate table后再放回來2. 也可以分段提交,樓上也提到了3. 專門使用一個(gè)大回滾段4. 假如確認(rèn)將來不需要做恢復(fù),改為非歸檔模式,刪除完改回來再做個(gè)備份.專家給出的解決方案:有條件的分步刪除數(shù)據(jù)表中的記錄--創(chuàng)建測試表create table test as select * from dba_objects;Table created.--創(chuàng)建刪除表的存儲過程create or replace procedure deleteTab--插入語句 SQL> insert into test select * from dba_objects;6374 rows created.SQL> /6374 rows created.SQL> /6374 rows created.SQL> commit;--創(chuàng)建刪除的存儲過程create or replace procedure deleteTab /** ** Usage: run the script to create the proc deleteTab **;;;;;in SQL*PLUS, type 'exec deleteTab('Foo','ID>=1000000','3000');' **;;;;;to delete the records in the table 'Foo', commit per 3000 records. **;;;;Condition with default value '1=1' and default Commit batch is 10000. **/ ( p_TableName;in;varchar2,;-- The TableName which you want to delete from p_Condition;in;varchar2 default '1=1',;-- Delete condition, sUCh as 'id>=100000' ;p_Count;;;;;in;varchar2 default '10000';-- Commit after delete How many records ) as pragma autonomous_transaction; n_delete number:=0; begin while 1=1 loop EXECUTE IMMEDIATE 'delete from 'p_TableName' where 'p_Condition' and rownum <= :rn' USING p_Count; if SQL%NOTFOUND then exit; else n_delete:=n_delete + SQL%ROWCOUNT; end if; commit; end loop; commit; DBMS_OUTPUT.PUT_LINE('Finished!'); DBMS_OUTPUT.PUT_LINE('Totally 'to_char(n_delete)' records deleted!'); end; /--執(zhí)行語句SQL> exec deleteTab('TEST','object_id >0','10000')你看看執(zhí)行結(jié)果我試驗(yàn)過，效果還可以