OraFAQ Forum: SQL & PL/SQL » Merge statement

Home » SQL & PL/SQL » SQL & PL/SQL » Merge statement - Intermediate commit (ORACLE 11G)

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

Merge statement - Intermediate commit [message #572496]

Wed, 12 December 2012 08:55

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.

Report message to a moderator

Re: Merge statement - Intermediate commit [message #572497 is a reply to message #572496]

Wed, 12 December 2012 08:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: Merge statement - Intermediate commit [message #572513 is a reply to message #572497]

Wed, 12 December 2012 11:55

Kevin Meade
Messages: 2103
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

Report message to a moderator

Re: Merge statement - Intermediate commit [message #572515 is a reply to message #572513]

Wed, 12 December 2012 12:29

Bill B
Messages: 1971
Registered: December 2004

Senior Member

The 1 million dollar question is what is the error when it "blows out"?

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 24 12:50:13 CDT 2025