Home » SQL & PL/SQL » SQL & PL/SQL » Deleting a large number of rows... (Oracle 10g R2 Windows 2003 Server)
Deleting a large number of rows... [message #378502] Tue, 30 December 2008 15:12 Go to next message
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

Re: Deleting a large number of rows... [message #378510 is a reply to message #378502] Tue, 30 December 2008 15:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Suggestions?

Search, this has been asked about one zillion times.

Regards
Michel
Re: Deleting a large number of rows... [message #378513 is a reply to message #378502] Tue, 30 December 2008 15:31 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Maybe you may find these threads interesting:
http://www.orafaq.com/forum/m/331483/96705/?srch=delete+loop#msg_331483.
Also search for ORA-01555 snapshot too old as you will often face it with the code you posted.
Re: Deleting a large number of rows... [message #378521 is a reply to message #378513] Tue, 30 December 2008 19:09 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at the ROWID RANGE technique.

But by far the better method is to partition your tables and use TRUNCATE/DELETE partition.

Ross Leishman
Re: Deleting a large number of rows... [message #378545 is a reply to message #378521] Tue, 30 December 2008 22:47 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
@rleishman & MCadot: I suggest to use bulk collect in this situation, don't I?
Re: Deleting a large number of rows... [message #378555 is a reply to message #378545] Tue, 30 December 2008 23:27 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sure, if you like ORA-01555 errors.
Previous Topic: What is the difference between these 2 sql statements
Next Topic: Decode function in select statement
Goto Forum:
  


Current Time: Wed Nov 13 05:32:54 CST 2024