I need urgent help from you guyes.
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.
Thanks in advance.
I think you'll be better off writing a PL/SQL procedure using collections (VARRAY, PL/SQL Tables..etc..) and using "Balk Binding" will speed up thing a lot.
Thanks for the prompt reply..
What is Balk Binding?
How about using the TRUNCATE command. This is fast. The only thing different about truncate is that rollback segments are not used. The data is deleted for good.
Have you considered Partitioning
Have you considered partitioning your table based on a range of the key values. ( may be year in your case)
that way you can drop the old partition whenever you dont need it.
Of course there are many gotchas regarding the administration of partitioned tables.
here is the small pl/sql script to delte the records with intermediary commits.
x number := 0;
for i in 1..5
x := x+500;
delete from dept where rownum < x;
that proc only 5 times in loop, i.e. if in ur table there are 2500 rows are there, then it will be delete 5 times, at each time it deletes 500 rows. If assume 3000 rows are there then still in ur table 500 records existed,i.e., its deletes only 2500 rows. so according to ur rows u may increase the loop capacity.
If loop 1..6 then all rows are existed, vice versa..
Click Here to Expand Forum to Full Width