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.