Posted on 2006-08-24 21:27
rednight 閱讀(2154)
評論(0) 編輯 收藏
從網(wǎng)上搜的帖子,忘記是哪里的,保存在這里以便隨時查看.
在使用delete語句刪除數(shù)據(jù)時，數(shù)據(jù)庫是要做日志記錄的，以便將來可以恢復(fù)數(shù)據(jù)，可是我在刪除上百萬條數(shù)據(jù)時，十分緩慢甚至死機(jī)，請問有沒有什么好方法?
網(wǎng)友觀點(diǎn)一:
create or replace procedure delete_table
is
i 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)二:
|
這個是我平常用來批量刪除數(shù)據(jù)，每500條數(shù)據(jù)提交一次。
DECLARE
CNT NUMBER(10):=0;
I NUMBER(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,'MM')='01';
FOR I IN 1..TRUNC(CNT/500)+1 LOOP
DELETE FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,'MM')='01' AND ROWNUM<=500;
COMMIT;
END LOOP;
END;
|
專家意見:幾個辦法:
1. 如果刪除的數(shù)據(jù)是大部分,建議使用樓上的方法把要保留的數(shù)據(jù)放在一個臨時表里,truncate table后再放回來
2. 也可以分段提交,樓上也提到了
3. 專門使用一個大回滾段
4. 如果確認(rèn)將來不需要做恢復(fù),改為非歸檔模式,刪除完改回來再做個備份.
專家給出的解決方案:
|
有條件的分步刪除數(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é)果我試驗過，效果還可以
|