How to delete a million rows and commit every 1000 rows? 1

[hallux]

Hello,
Can anybody point me in the right direction for instructions on deleting a million rows and applying a commit every 1000 rows? Using Oracle 8.1.7.
-Hallux

 

[SantaMufasa]

Hallux,

Tell us more...How many rows total in the table? How large are the gaps between the rows to be deleted?

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:28 (10Dec03) GMT, 15:28 (10Dec03) Mountain Time)

 

[hallux]

Mufasa,
10 million rows total in table. How do I find out the gaps between the rows to be deleted?
-Hallux

 

[vc123]

If you want to delete so many rows, it may be more efficient to create a new table with the rows you want to preserve in the nologging mode:

create table t2 nologging as select * from t1 where <rows you want keep>;
drop table t1;
rename t2 to t1;

Rgds.

 

[sem]

You may try

begin
LOOP
DELETE FROM <TABLE> WHERE <CONDITION> AND ROWNUM<=1000;
EXIT WHEN SQL%ROWCOUNT=0;
COMMIT;
END LOOP;
end;

This may take a long time, but can be done in parts

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

Next time consider to partition the table