Home » SQL & PL/SQL » SQL & PL/SQL » Merge statement - Intermediate commit (ORACLE 11G)
Merge statement - Intermediate commit [message #572496] Wed, 12 December 2012 08:55 Go to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
All,

I am using Merge statement to copy data from one table to other,
merge into tabA a
using tabB b
on a.id = b.id
when match
update (
)
when not matched
(insert )


This is working all fine, as SQL, but when there is large volume, it blows out as there are intermediate commits for this ?
Can somebody please tell me whats the best way to put intermediate commits.
Re: Merge statement - Intermediate commit [message #572497 is a reply to message #572496] Wed, 12 December 2012 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>Can somebody please tell me whats the best way to put intermediate commits.
COMMIT inside LOOP is NEVER optimal solution

> it blows out
above is NOT an Oracle error code & message.
Is COPY & PASTE broken for you?
Re: Merge statement - Intermediate commit [message #572513 is a reply to message #572497] Wed, 12 December 2012 11:55 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
short answer is NO. merge is just a SQL statement like any other, and you cannot commit in the middle of a SQL statement. That is like asking

INSERT INTO ... SELECT * FROM ...;

can I commit when the insert is half done?

You want to commit in the middle, use two statements. Of course there is no guarantee that this will be faster; depends upon how you write the SQL, and it certainly is not the same semantically because you have created two transactions instead of one. Lastly you will need to figure out how to deal with failure. Suppose the first statement dies and the second succeeds or vice-versa. How do you recover and/or complete the operation?

Kevin
Re: Merge statement - Intermediate commit [message #572515 is a reply to message #572513] Wed, 12 December 2012 12:29 Go to previous message
Bill B
Messages: 1049
Registered: December 2004
Senior Member
The 1 million dollar question is what is the error when it "blows out"?
Previous Topic: Partition a Table by List and Interval
Next Topic: How to create a procedure to read data from database and export it into .csv format
Goto Forum:
  


Current Time: Wed Apr 16 14:13:34 CDT 2014

Total time taken to generate the page: 0.16142 seconds