Deleting a large number of rows... [message #378502] |
Tue, 30 December 2008 15:12 |
phekno
Messages: 1 Registered: December 2008
|
Junior Member |
|
|
I have a table that is used to store large amounts of raw data (i.e. date, value, id, etc.) Contractually we are required to keep 18 months of data, but to be on the safe side we like to hang on to 24 months. Anything older than 24 months has to be deleted. Typically we could see around 5-10 million rows of growth per month in this one table. I haven't exactly been diligent about deleting on a weekly or daily basis (this is something I want to do) so I have around 6-7 months worth of deletes to do, which, last I checked was around 41,247,430 rows. Anyway, just doing a delete on this many rows would bring my server (shared amongst several databases, all with their own instance) to its knees. Does anyone have any ideas as to how to delete say 10000 rows at a time until the entire delete is done? I'm testing using something like this:
declare
i number := 0;
cursor s1 is SELECT rowid, r.* FROM hugetable r WHERE timestamp < '29-DEC-2006';
begin
for c1 in s1 loop
delete from hugetable
where rowid = c1.rowid;
i := i + 1; -- Commit after every X records
if i > 10000 then
commit;
i := 0;
end if;
end loop;
commit;
end;
/
where hugetable is the table with all the rows and right now it's running but I'm not sure how effective (i.e. efficient) it will be. Suggestions?
[Updated on: Tue, 30 December 2008 15:21] Report message to a moderator
|
|
|
|
|
|
|
|