need suggestion in deletion for five tables at a time
577162Nov 12 2009 — edited Oct 3 2011
I need some suggestion regarding a deletion and i have the following scenario.
tab1 contains 100 items.
for one item tab2..6 tables contain 4000 rows.So the loop will run for each item and will delete 20,000 lines and will do a commit.
Currently for 5,00,000 deletion it is taking 1 hr.All the tables and indexes are analysied.
SELECT api.item FROM tab1 api WHERE api.delete_item_ind = 'Y';
type p_item IS TABLE OF tab1.item%type;
FETCH C_CHECK_DELETE_IND bulk collect INTO act_p_item limit 5000;
FOR i IN 1..act_p_item.count
DELETE FROM tab2 WHERE item = act_p_item(i);
DELETE FROM tab3 WHERE item = act_p_item(i);
DELETE FROM tab4 WHERE item = act_p_item(i);
DELETE FROM tab5 WHERE item = act_p_item(i);
DELETE FROM tab6 WHERE item = act_p_item(i);
exit when C_CHECK_DELETE_IND%notfound;
Hope i have explained the scenario.Can you please suggest me the right approach.
Thanks in advance.