Delete from a table is running for many days to complete
610257Jul 10 2008 — edited Jul 11 2008i have procedure where in i am executing the delete query in the following manner.
sql_string := 'DELETE FROM param_tbl_name t1 WHERE t1.aq_id NOT IN (SELECT t2.aq_id FROM tbl_nif t2 where t1.aq_id=t2.aq_id)';
LOOP
EXECUTE IMMEDIATE sql_string;
deleted_cnt := SQL%ROWCOUNT;
COMMIT;
EXIT WHEN deleted_cnt = 0;
END LOOP;
the param_tbl_name has aq_id which keep updating with different values.
Probably this might be the reason why this particular table is taking long time to delete.
I need suggestion from you all how effectively we can delete rows in the table. This procedure is called from cronjob(on solaris system) everyday at a particular time.
Is there any way that we can delete records.(the param_tbl_name contains million records minimum at any given time!!)
thanks in advance.