Massive Delete Operation

arunabha_datta · #1 (**permalink**) 06-11-2008

Hi, i need some help regarding massive delete operation. One table is having million of rows and 9 indexes on it. It's running on production. But it seeems the deletion of records using bulk binds and using rowid is also very slow. It's not possible io increase the rb segment in the db. We're getting snapshot too old error also thats because of this frequent commit i guess. It's not possible to make partition in the table. Is there any flaw in teh below written code which can be rectified provided all teh restriction i discussed before are met. Many thanks in advance. Sample code::

Code:

declare type xxx is table of rowid index by binary_integer; 
yyy xxx; 
cursor cur_del_prm is select rowid as person_id from prem_od where person_id in (select person_id from person_t where run_id =  ); 
begin 
open cur_del_prm; 
loop fetch cur_del_prm bulk collect into yyy limit < global variable g_prem_bulk_del_limit ... Currently set to 50k > ; 
if yyy.count = 0 then exit; 
end if; 
forall idx in yyy.first..yyy.last delete from prem_od where rowid = yyy(idx); 
commit; 
end loop; 
close cur_del_prm; 
yyy.delete; 
end;

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Active Screen Operation	Geek_Guest	QTP	1	12-27-2007 09:01 AM
array of string operation	shivaram.cunchala	C and C++	8	11-29-2007 05:50 AM
Delete values in single a row without using DELETE keyword.	Geek_Guest	SQL	22	10-25-2007 06:50 AM
Object operation failed	Geek_Guest	Rational Robot	0	04-16-2007 09:05 AM
How to make sure that the operation on database is successful?	sivaprasademani	SQL Server	2	04-04-2007 02:03 AM

Massive Delete Operation

Massive Delete Operation

Similar Threads