OraFAQ Forum: SQL & PL/SQL » How to do Implied Commit

Home » SQL & PL/SQL » SQL & PL/SQL » How to do Implied Commit

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to do Implied Commit [message #301234]

Tue, 19 February 2008 14:43

e3339
Messages: 11
Registered: May 2007

Junior Member

Hello,

I have to delete millions of rows from a table while keeping millions of others. I was wondering is there a way of writing a delete command that will commit the delete after every 50,000 rows as opposed to waiting for me to type commit when the delete is done.

DELETE from test
WHERE param < 3;

Report message to a moderator

Re: How to do Implied Commit [message #301235 is a reply to message #301234]

Tue, 19 February 2008 14:45

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No, a statement is a whole, it is atomic, it either completly succeeds or completly fails.

Regards
Michel

Report message to a moderator

Re: How to do Implied Commit [message #301246 is a reply to message #301234]

Tue, 19 February 2008 16:33

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

As you might guess, this is not something that nobody has ever come across before. Deleting millions of rows is rarely a good idea. See the link below for a intro on why.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:6407993912330

But if you still think delete is a good idea, you might want to revisit the question of why you need intermittent commit. Unless you have a really, really good reason why you need them ("Because somebody said I should", is not a good reason), just don't do them.

Report message to a moderator

Previous Topic:	Invalid number error when trying to retrieve numeric data from varchar2 columns.
Next Topic:	How to achieve Global Temporary Functionality by using the Table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 18 09:33:00 CDT 2025