The best way to it with a lower impact to the users and a very good time is:
1 – If possible drop the indexes (it´s not mandatory, it will just save time)
2 – Run the delete using bulk collection like the example below
declare
cursor crow is
select rowid rid
from big_table where filter_column=’OPTION’ ;
type brecord is table of rowid index by binary_integer;
brec brecord;
begin
open crow;
FOR vqtd IN 1..500 loop
fetch c bulk collect into brec limit 20000;
forall vloop in 1 .. brec.count
delete from big_table where rowid = brec(vloop);
exit when crow%notfound;
commit;
dbms_lock.sleep(5);
end loop;
close crow;
end;
/
3 – Adjust the values vqtd , limit and sleep to best to your case.
In my case using these values (vqtd:=500 , limit:=20000 and sleep:=5) each whole execution took an hour.
Try begin with vqtd 10 , and then check your redo/archive generation and the v$system_event, to make adjust for your environment.
Bulk delete gives you a lot of control regarding the undo usage.
Oracle Logbook 
Heliodias, This is a nice one! I was thinking exactly parallel to this for the past few days, but was unsure whether my idea was a good option! After looking at your post, Im sure this is a good option. We both are in the same boat! Using Rowid while bulk deleting, will give an improved performance, as looking up the data record with ROWID is much more efficient than using index. The below is exactly like yours with some simple modifications
— removed vloop
— Handled Bulk Exception
DECLARE
CURSOR crow is
SELECT rowid
FROM big_table WHERE filter_column=’OPTION’ ;
TYPE brecord IS TABLE OF rowid INDEX BY BINARY_INTEGER;
brec brecord;
v_limit NUMBER := 20000; — Adjust this value according to your env
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
OPEN crow;
LOOP
FETCH c BULK COLLECT INTO brec LIMIT v_limit;
IF brec.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE (‘Bulk deletion completed’);
COMMIT;
CLOSE crow;
EXIT;
ELSE
FORALL vloop in brec.FIRST .. brec.LAST SAVE EXCEPTIONS
DELETE FROM big_table WHERE rowid = brec(vloop);
COMMIT;
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line(‘Number of errors is ‘ || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line(‘Error ‘ || i || ‘ occurred during ‘|| ‘iteration ‘ || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line(‘Oracle error is ‘ || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
COMMIT;
IF crow%ISOPEN THEN
CLOSE crow;
END;
Comment by metallicatony — April 5, 2010 @ 10:43 pm |
EXCEPTION WHEN dml_errors THEN errors := SQL%BULK_EXCEPTIONS.COUNT; dbms_output.put_line(‘Number of errors is ‘ || errors); FOR i IN 1..errors LOOP dbms_output.put_line(‘Error ‘ || i || ‘ occurred during ‘|| ‘iteration ‘ || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); dbms_output.put_line(‘Oracle error is ‘ || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); END LOOP; COMMIT; IF crow%ISOPEN THEN CLOSE crow;END;
+1
Comment by Wendi Ferner — July 1, 2010 @ 11:46 pm |
Wonderful sharing article about drop table fastest way. Thanks a lot for sharing.
Comment by Jack Nicholson — September 23, 2010 @ 9:02 pm |
Thanks for the great post. It helps me.
If insert also be done same way, will it be similarly with improved performance.
Can this way be used for below kind of huge insert query or you see any efficient alternate way for these kind of inserts.
INSERT INTO huge_table_mmdd
SELECT * FROM huge_table;
Comment by Sucheta — May 9, 2012 @ 4:22 am |
Hi Sucheta,
To improve a insertion from select, you should use the hint append and disable the redolog for the operation.
(With the append hint)
insert /*+append */ into huge_dest
(select * from main_huge);
1000000 rows created.
Elapsed: 00:01:00.30
(With the append hint and nologging (doing the operation without redolog))
insert /*+append */ into huge_dest nologging
(select * from main_huge);
Elapsed: 00:00:31.02
After use the nologging, you must do a backup of your datafiles.
Comment by heliodias — May 9, 2012 @ 4:46 pm |
Thanks a lot for spending time to publish “Best way to delete millions rows from hundred millions table
Oracle Logbook”. Thanks yet again -Maisie
Comment by http://tinyurl.com/slasbiern26187 — February 3, 2013 @ 10:46 am |
Hello, I know the thread is old. I have a similar setup to delete hundreds of millions of rows. But it always fills up the UNDOTS. I do not have the dbms_lock.sleep(5) line.
Does this line affects the filling up of the Undo tablespace. Other parameter is limit=5000
Count of rows in tables – around 10M – 700M.
Comment by jsixface — March 27, 2014 @ 8:48 pm |