Home » SQL & PL/SQL » SQL & PL/SQL » Question on insert statement (Oracle 11.2.0.3.0, Linux)
Question on insert statement [message #568379] Thu, 11 October 2012 04:42 Go to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

Can we execute more than one insert statements at a time (eg 10) in database and give
commit at the end of insert statements or else give a commit one by one after each insert statements ?

Can you suggest which method to go for and why ?

Regards

[Updated on: Thu, 11 October 2012 04:43]

Report message to a moderator

Re: Question on insert statement [message #568382 is a reply to message #568379] Thu, 11 October 2012 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to commit ONLY when the transaction is done.
This is THE rule of transactional database.

Regards
Michel
Re: Question on insert statement [message #568387 is a reply to message #568382] Thu, 11 October 2012 05:04 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Quote:
You have to commit ONLY when the transaction is done

You mean to say we have to commit after each insert statement is done ?
if yes, why not we commit at the end after executing all the insert statments? can you explain ?

Thank you
Re: Question on insert statement [message #568393 is a reply to message #568387] Thu, 11 October 2012 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is one insert a transaction for your application?

Regards
Michel
Re: Question on insert statement [message #568447 is a reply to message #568393] Thu, 11 October 2012 10:38 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
yes
Re: Question on insert statement [message #568449 is a reply to message #568447] Thu, 11 October 2012 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
(I doubt this is true but anyway) So commit every insert but it will be very bad for performances not only yours but every one connected to the database.

Regards
Michel
Re: Question on insert statement [message #568483 is a reply to message #568449] Fri, 12 October 2012 03:03 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Confused Can you tell me which method to follow for better performance ?
Re: Question on insert statement [message #568484 is a reply to message #568483] Fri, 12 October 2012 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Commit only once at the end.

Regards
Michel
Re: Question on insert statement [message #568508 is a reply to message #568484] Fri, 12 October 2012 10:26 Go to previous message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Smile Thank you
Previous Topic: How to use if exist condition in oracle
Next Topic: Convert date to numeric
Goto Forum:
  


Current Time: Thu Sep 18 17:03:06 CDT 2014

Total time taken to generate the page: 0.08383 seconds