Home » SQL & PL/SQL » SQL & PL/SQL » Bulk delete
Bulk delete [message #254038] Wed, 25 July 2007 10:35 Go to next message
Alps
Messages: 28
Registered: November 2006
Location: Toronto
Junior Member
Hi Guys,
My database is 7.3.4 on aix. I want to do a bulk delete for reorg. I am wondering is there any script for that and if it is then kindly let me know where can i put my constraint for specific dates to be purged.

Thanks a lot in advance
Re: Bulk delete [message #254072 is a reply to message #254038] Wed, 25 July 2007 12:27 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Alps wrote on Wed, 25 July 2007 11:35
for specific dates to be purged.



I don't understand. Just use a WHERE clause for the date range.
Re: Bulk delete [message #254073 is a reply to message #254072] Wed, 25 July 2007 12:36 Go to previous messageGo to next message
Alps
Messages: 28
Registered: November 2006
Location: Toronto
Junior Member
yes you r right.....but the table is having 1 millon rows....so its not fisiable to put a where clause.....otherwise the performance will hit. Do you have any pl/sql scripts doing commit after every 1000 or 2000 rows and thats makes the bulk delete
Re: Bulk delete [message #254075 is a reply to message #254038] Wed, 25 July 2007 12:43 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Commits within a loop are generally not a good thing.

My advice would be to do a select to gather up the rowid or primary key of the records that you want to purge, then do your deletes based on that.

Re: Bulk delete [message #254077 is a reply to message #254075] Wed, 25 July 2007 12:49 Go to previous messageGo to next message
Alps
Messages: 28
Registered: November 2006
Location: Toronto
Junior Member
can you give me an example on how to capture rowid for specific dates and then try to delete data's for those specific date...
thanks
Re: Bulk delete [message #254139 is a reply to message #254077] Wed, 25 July 2007 21:19 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is a technique called Rowid Range that is described here.

It is also described somewhere on asktom.oracle.com, where there may even be some code snippets.

Ross Leishman
Previous Topic: how could this piece of code not working
Next Topic: How to set/change the maximum size of an existing table?
Goto Forum:
  


Current Time: Tue Dec 06 12:30:10 CST 2016

Total time taken to generate the page: 0.05779 seconds