OraFAQ Forum: SQL & PL/SQL » Question on insert statement

Home » SQL & PL/SQL » SQL & PL/SQL » Question on insert statement (Oracle 11.2.0.3.0, Linux)

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

Question on insert statement [message #568379]

Thu, 11 October 2012 04:42

Jack14
Messages: 333
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

Michel Cadot
Messages: 54125
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

Report message to a moderator

Re: Question on insert statement [message #568387 is a reply to message #568382]

Thu, 11 October 2012 05:04

Jack14
Messages: 333
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

Report message to a moderator

Re: Question on insert statement [message #568393 is a reply to message #568387]

Thu, 11 October 2012 05:10

Michel Cadot
Messages: 54125
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is one insert a transaction for your application?

Regards
Michel

Report message to a moderator

Re: Question on insert statement [message #568447 is a reply to message #568393]

Thu, 11 October 2012 10:38

Jack14
Messages: 333
Registered: December 2011
Location: INDIA

Senior Member

yes

Report message to a moderator

Re: Question on insert statement [message #568449 is a reply to message #568447]

Thu, 11 October 2012 11:33

Michel Cadot
Messages: 54125
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

Report message to a moderator