Home » SQL & PL/SQL » SQL & PL/SQL » Bulk delete (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
Bulk delete [message #388358] Tue, 24 February 2009 08:05 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi,

I have a table A which has million records, and one of the column is a date. All records prior to 01-FEB-2009 should be deleted.

I cannot run the below code considering the amount of records or rows in the table A.

Begin
Delete from A where trunc(date_column) < '01-FEB-2009';
commit;
End;


Can anyone please suggest an idea where we can delete the rows prior to 01-FEB-2009 and also have a total count of how many were deleted?
Re: Bulk delete [message #388364 is a reply to message #388358] Tue, 24 February 2009 08:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
other than rewriting '01-FEB-2009' as to_date('01-FEB-2009','dd-mon-yyyy'), that's a prety good way to do it.

Why do you feel that you can't run this?
Re: Bulk delete [message #388366 is a reply to message #388364] Tue, 24 February 2009 08:36 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
There are many rows (in millions), I feel
1. It may take long to execute OR
2. It may throw up an error (something to do with size)

I have heard about giving commits at regular interval, can you give me an idea as to how to implement it ?
Re: Bulk delete [message #388367 is a reply to message #388366] Tue, 24 February 2009 08:38 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
jagannathkiran wrote on Tue, 24 February 2009 14:36
There are many rows (in millions), I feel
1. It may take long to execute OR
2. It may throw up an error (something to do with size)


So you haven't done any testing? You're just guessing that it can't be done?
Quote:

I have heard about giving commits at regular interval, can you give me an idea as to how to implement it ?

Welcome to the world of the 01555 snapshot too old error.
Re: Bulk delete [message #388369 is a reply to message #388367] Tue, 24 February 2009 08:42 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Quote:

01555 snapshot too old error



Ah that's the one, I had come across this error before too, but i dint quite remember it, So how do i perform the multiple commits ?
Re: Bulk delete [message #388371 is a reply to message #388369] Tue, 24 February 2009 08:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Mulitple commits CAUSE 01555!
Re: Bulk delete [message #388373 is a reply to message #388371] Tue, 24 February 2009 08:46 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Oh my goodness !! Then please enlighten me as to how i can go ahead with this problem ?
Re: Bulk delete [message #388374 is a reply to message #388373] Tue, 24 February 2009 08:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Perform the delete as you posted (remember to add the slight alteration that was suggested by JRowbottom)
Re: Bulk delete [message #388375 is a reply to message #388373] Tue, 24 February 2009 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The best way is to do it in a single delete.
If you can't (because of lack of undo space, for instance) then delete 3 months by 3 months for instance.
Now you should partition your table.

Regards
Michel
Re: Bulk delete [message #388376 is a reply to message #388373] Tue, 24 February 2009 08:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In the first instance, try doing the SQL that you posted - it's the simplest and most reliable way of deleting records.

How many records are in the table, and how many are due to be deleted?

If speed does become an issue, look at:
1) disabling any foreign key constraints that point at the table. You will need to delete any records in child tables by hand, but that may still be quicker.

2) Disabling any indexes on the table - you can rebuild them afterwards, off a much smaller set of data

3) If there are few FK constraints that point to this table, consider creating another table that contains only the rows that you want to keep, dropping the original table, and renaming the new table to replace the original.
Re: Bulk delete [message #388378 is a reply to message #388375] Tue, 24 February 2009 08:51 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Yeah , I will make the changes and then do the delete, but as of now I am running late for my BUS, thanks a ton for your advice guys, will come back tomorrow with the results, Good Day Smile
Re: Bulk delete [message #489911 is a reply to message #388378] Wed, 19 January 2011 22:23 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Thanks for all the solutions Smile
Re: Bulk delete [message #489925 is a reply to message #489911] Thu, 20 January 2011 01:58 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Wow, that was one long bus trip.
Re: Bulk delete [message #489932 is a reply to message #489925] Thu, 20 January 2011 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing

Re: Bulk delete [message #489933 is a reply to message #489925] Thu, 20 January 2011 02:17 Go to previous message
Roachcoach
Messages: 1421
Registered: May 2010
Location: UK
Senior Member
If you're deleting more than a decent percentage of the table (I leave it to you to decide what this is, its an "operational" decision) then consider creating a container table with the records you wish to keep, dropping the old table and renaming the container table.

Of course to get the records deleted you'd need to do a quick count but that shouldn't be hard.


Fake edit: On re-reading the OP for sizes to confirm my suggestion - the table is only a million rows - that shouldn't throw performance issues, at least I'd not have expected that.

real edit: Failing to spot the thread necro this early in the morning.... -1 to me.

/sigh.

[Updated on: Thu, 20 January 2011 02:18]

Report message to a moderator

Previous Topic: Need a REGEXP_REPLACE algorithm
Next Topic: char(1) vs varchar(1char)
Goto Forum:
  


Current Time: Mon Dec 05 04:34:04 CST 2016

Total time taken to generate the page: 0.09226 seconds