Home » SQL & PL/SQL » SQL & PL/SQL » Which of the following is best- MERGE or update/insert trigger?
Which of the following is best- MERGE or update/insert trigger? [message #193248] Fri, 15 September 2006 07:29 Go to next message
sharath160
Messages: 9
Registered: August 2006
Junior Member
I have two options to update/insert into a table, can somebody shed a light on which is better in performance point of view. 1) Trigger option, update/insert or 2) MERGE option. Both the methods cater to the same requirement.

1) BEGIN
CREATE OR REPLACE TRIGGER upload_trigger
AFTER INSERT ON TEMP_CATALOG
FOR EACH ROW
update TEST1 set delivery_priority='22' where order_type='ZSEZ';
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO TEST1 ( ENTERPRISE_CODE, SELLER_ORG_CODE, ORDER_TYPE,
DELIVERY_PRIORITY ) VALUES (
'HP', 'HP_EMEA', 'ZSEQ', '33');
END IF;
END;

Here we try updating each record, if nothing is updated (SQL%ROWCOUNT = 0) then INSERT happens.

2) Here the whole new data is loaded into a temp table and then MERGE is used.

BEGIN
MERGE INTO TEST1 et
USING ( SELECT * FROM TEST1_H ) es
ON ( et.DELIVERY_PRIORITY = es.DELIVERY_PRIORITY AND et.ENTERPRISE_CODE = es.ENTERPRISE_CODE)
WHEN MATCHED THEN
UPDATE
SET et.SELLER_ORG_CODE = es.SELLER_ORG_CODE
, et.ORDER_TYPE = es.ORDER_TYPE
WHEN NOT MATCHED THEN
INSERT
( et.DELIVERY_PRIORITY, et.ENTERPRISE_CODE, et.SELLER_ORG_CODE, et.ORDER_TYPE )
VALUES
( es.DELIVERY_PRIORITY, es.ENTERPRISE_CODE, es.SELLER_ORG_CODE, es.ORDER_TYPE );
END;

Here the target table(TEST1) and source table(TEST1_H) are taken and each row from source_table(TEST1_H) is either updated or inserted in target_table(TEST1). Probably a cron job could do this MERGE instead of a resource intensive trigger?
Re: Which of the following is best- MERGE or update/insert trigger? [message #193250 is a reply to message #193248] Fri, 15 September 2006 07:34 Go to previous messageGo to next message
sharath160
Messages: 9
Registered: August 2006
Junior Member
Some more information.. the new data is of 2GB size and more than 10k records.
Re: Which of the following is best- MERGE or update/insert trigger? [message #193311 is a reply to message #193248] Fri, 15 September 2006 11:18 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Definitely MERGE.
Re: Which of the following is best- MERGE or update/insert trigger? [message #198024 is a reply to message #193248] Fri, 13 October 2006 20:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I'd go with the trigger option myself. But then I don't like Merge. It is one of those things I consider "unfriendly" code constructs.

Oracle says it does exactly what they intended for it to do. That's nice. But it doesn't do what people expect it to do, or want it to do. Every time I use it I have problems with it. Yes, sometimes it is me being stupid, but most of the time it is because of the way merge behaves. I guess to really appreciate this, you have to work with more sophisticated databases to see how different pieces interact with each other.

Anyway, I have been doing oracle for 23 years. I am no genius but then again I ain't no dummy either. If I have problems with merge, I figure the people I work with won't have a chance.

My advice:

If you are going to use merge, understand it intimately, then try it out under several situations including trigger heavey databases. Watch carefully how the triggers are executed and make sure you are happy with how the rest of your systems will react when you use merge. Also execute the merge under various data scenarios, including those where there is no data to initially drive the merge with. Lastly, consider the general skill level of those you work with. Is this a feature they can adapt to? Writing code doesn't have just one goal, there are many, one of which is to create programs people besides you can grow when you aren't around. If you think some people you work with will have problems moving up to merge, reconsider its use.

Good luck, Kevin
Re: Which of the following is best- MERGE or update/insert trigger? [message #198068 is a reply to message #198024] Sat, 14 October 2006 04:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Kevin Meade wrote on Sat, 14 October 2006 03:08

If I have problems with merge, I figure the people I work with won't have a chance.
Hmm, sounds like you have confidence in your colleagues' abilities...
Not all that's new (and MERGE has been around for some time now) is bad. It's like ANSI joins: not bad but for an experienced Oracle veteran it might be hard to use.

MHE

[Updated on: Sat, 14 October 2006 04:27]

Report message to a moderator

Re: Which of the following is best- MERGE or update/insert trigger? [message #198074 is a reply to message #193248] Sat, 14 October 2006 06:19 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Actually I have high confidence in their abilities. I'm an internal contractor for my company. The people I work with aren't dumb, in fact they are rather good at their jobs. They just have jobs with specific roles; in an environment that does not give them time to spend learning lots of new tricks. They mostly learn what they need to do there job and not much more.

In an environment where change comes slow, this works out pretty well as things go along smooth most of the time. Everybody knows what to expect: DBAs know what kind of code developers will produce, Developers know the logical and physical commodities that will be produced for them to exploit, nobody jumps too fast into something new, few new problems to deal with. But, when it comes time to introduce new ideas and techniques to address opportunity as vs. maintaining the status quo, it is not so good. There is a strong resistance to change. They find it difficult to get the time needed to learn new features which makes new features risky. This means there are lots of lost opportunities. Indeed, this has become a big part of my job as a "roving Oracle Specialist" in my company, to reduce the learning curve, and help Oracle areas find real value in new features of Oracle without upsetting the apple cart.

My point about merge is simply that everyone thinks they understand it. And on the surface I suppose they do, but under the covers, merge operates a certain way, and it is not intuitive or expected. Given its roots as a form of "UPSERT", it might be reasonable to assume it simply does an update otherwise an insert. Well.. yes/and no.. you have to spend some time with it under several conditions to get a feel for it. My experiences with it so far have lead me to the conclusion that I don't want to use it. I am considered a pretty aggressive guy, not shy of taking risk, so for me to be negative on an Oracle feature is a little bit of a head turner for those I work with.

I don't want to belabor the issue, or tell people how to write their code. I simply suggest that if you going to use merge, then you need to understand how it behaves in the code and system contexts I mentioned earlier. It is not just a straight forward replacement for "try this update, if 0 rows updated then try this insert".

You make an astute point too about Experienced Veterans; that we do sometimes resist change ourselves. I am in fact suffering a bit with the new ANSI join syntax. After analyzing it I do see it has several advantages over traditional Oracle syntax, yet I still have not adopted it. Ah well, we all can improve what we do right?

Thanks, Kevin
Previous Topic: to get actual age (merged)
Next Topic: problem in dbms_random.
Goto Forum:
  


Current Time: Sun Dec 04 18:33:31 CST 2016

Total time taken to generate the page: 0.07158 seconds