I am trying to delete from one of the table and it is expected to delete millions of rows everytime.
The SQL , I am using is as below.
delete from xyz where time_key
in (select time_key from dss_dims_time where period_year = 2000) AND rownum < 10001
I am trying to delete 10000 rows at a time , in order to avoid blowing up the rollback segment.
I am thinking of writing a PL/SQL procedure in which a cursor having all rowids matching for the condition and deleting one by one in a loop and checking the count , if count is = 10000 then commit it. Pl let me know your ideas and suggestion as soon as possible.
Instead of deleting million rows (assume that 70% rows are going to be deleted), why don't you copy the required rows (30%) into another table (temp), and then truncate the original table. Later insert from the new table (temp).
Thanks for all suggestions. We are really thinking of the second option of creating a temp table and inserting records into it which we dont want to delete and truncate the original table and do the insert again.
I tried with the PL/SQL procedure that I mentioned earliar, it really worked well. We could reduse the delete time from 1.5 hrs to 12 mins. That was incredial gain in the performance.
Pl let me know if anybody has any more suggestions.