Home » SQL & PL/SQL » SQL & PL/SQL » MERGE - Statement
MERGE - Statement [message #231200] Sun, 15 April 2007 22:59 Go to next message
Messages: 294
Registered: February 2006
Senior Member

Please have a look and if any suggestion Please point it,
Basically what i am doing is , Have to load some Data ( less than 1 million ) from each file every day,
IT will first check for some criteria if ID's are found then it will update or else inserts if new records,
Can't use SQL Loader becz this is kicked from Application (Browser ), Thought of using creating external tables on fly and then insert, but the user accessing through browser has no grants on create table ..
Wanted to test with merge statement, becz plain insert and Updates are taking much more time,
Is this merge statemetn approach good, and moreover on this I am using
SELECT 'X' from dual ;
-- Is this going to be costly every time it uses Merge statement, I have to use this dual to other wise getting errors on Using Clause of Merge statement,

Any suggestions or recommendations.


Re: MERGE - Statement [message #231253 is a reply to message #231200] Mon, 16 April 2007 02:32 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Use MERGE only for multi-row merging. For single rows, as you have discovered it is POSSIBLE to use MERGE (with DUAL) but you shouldn't. It is just plain nasty and it will be no quicker than transactional INSERT/UPDATE statements.

You don't need to create the external table on the fly. If you always place the file in the same location, just move each day's file to that location and never drop the EOT. If you have sharing issues, you may need to write a wrapper that uses DBMS_LOCK to avoid one user blowing away another's file whilst it is loading.

If you don't use SQL*Loader or an EOT, how were you planning to get the data to the MERGE statement? Would the server-side Web logic process the file itself? If so, probably the best solution is to insert the incoming data into a permanent Global Temporary Table (don't create it on the fly) and then MERGE the results.

Ross Leishman
Previous Topic: Need Help With SQL Statement
Next Topic: ERROR : about utl_file package ....................
Goto Forum:

Current Time: Wed Jul 26 01:34:59 CDT 2017

Total time taken to generate the page: 0.18776 seconds