我有个没有经过测试的方法:
1.CREATE TABLE NEWTABLE AS SELECT * FORM OLDTABLE WHERE .......
2.DROP TABLE XXX PURGE
3.RENAME OLDTABLE TO OLDTABLE
应该可以减少redo和undo的写入吧,不过只有500条记录而已。
本帖最后由 solomon_007 于 2012-2-2 16:22 编辑
如果delete量比较大(比例较高就用7楼的),可参考下面。。。
declare
l_count number := 0;
l_total number := 0;
cursor cur is
select rowid from t where ...;
begin
for rec in cur loop
delete from t where rowid = rec.rowid;
l_total := l_total + 1;
l_count := l_count + 1;
if (l_count >= 1000) then
commit;
l_count := 0;
end if;
end loop;
commit;
dbms_output.put_line('deleted rows: ' || l_total);