Re: How to delete millions of record in a loop
So I have good and bad news !
Good news is I am not getting any more error messages !
Bad news is the script is not doing what it is supposed to do.
sql script:
use mydb;
set autocommit=0;
drop procedure delete_table_incrementally;
delimiter //
create procedure delete_table_incrementally()
modifies sql data
begin
repeat
DELETE FROM mytable where my_condition='ERROR-5000' order by id limit 3;
commit;
select count(*) FROM mytable where my_condition='ERROR-5000';
until row_count()=0
end repeat;
end;
//
delimiter ;
Query output:
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
I personally don't think, its working. As It should produce output as decreasing number of rows affected but that is not the case so what is going wrong ?
Subject
Written By
Posted
November 16, 2016 06:56AM
November 16, 2016 07:52AM
November 16, 2016 05:16PM
November 16, 2016 06:11PM
November 16, 2016 07:16PM
Re: How to delete millions of record in a loop
November 16, 2016 10:03PM
November 17, 2016 12:29AM
November 17, 2016 09:31AM
November 17, 2016 10:35AM
November 17, 2016 05:51PM
November 17, 2016 06:08PM
November 17, 2016 09:44PM
November 16, 2016 10:03PM
November 16, 2016 11:07PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.