This discussion is archived
5 Replies Latest reply: Jan 11, 2013 5:48 PM by Frank Kulash RSS

need help with Merge Statement

Kodiak_Seattle Newbie
Currently Being Moderated
I am on: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

I currently have an UPDATE Statement and while it works and is accurate, it takes 30 minutes. I've heard that MERGE INTO
can do the same thing and is just as accurate and much faster!

Here is the Query that I want to convert into a MERGE INTO Statement. I tried to do it my self, but I am getting errors and am simply not sure, since this is new to me.

Basically I want to update table ta on a matching condition from table tt for 2 columns ( GTP and UPDATE_DT ), for UPDATE_DT I want to insert the current System Date.
UPDATE /*+ PARALLEL (16) */  OLDER_Table ta

SET (ta.GTP, ta.UPDATE_DT) = 

                (SELECT /*+ PARALLEL (16) */ tt.GTP, SYSDATE
                 FROM NEWER_Table tt
                 WHERE ta.cust_id = tt.cust_id
                 AND ta.STAMP_DATE = tt.STAMP_DATE
                 AND ROWNUM = 1)

                 WHERE EXISTS (SELECT 1
                               FROM NEWER_Table tt
                               WHERE ta.cust_id = tt.cust_id
                               AND ta.STAMP_DATE = tt.STAMP_DATE
                               AND (NVL(ta.GTP, 'X') != NVL(tt.GTP, 'X'))); 
Thank you!
  • 1. Re: need help with Merge Statement
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results will be the contents of the changed table(s) when everything is finished.
    See the forum FAQ {message:id=9360002}

    MERGE can be much faster than UPDATE; sometimes there's no significant difference in speed.
    In multi-table situations like this, I've never seen a case where MERGE was slower than UPDATE, though sometimes UPDATE is easier to code, and just as fast.

    This may be what you want:
    MERGE INTO  older_table            dst
    USING  (
               SELECT  n.cust_id     
            ,        n.stamp_date
            ,        n.gtp
            ,        ROW_NUMBER () OVER ( PARTITION BY  n.cust_id
                                               ,           n.stamp_date
                             ORDER BY      n.gtp
                              )                    AS r_num 
            FROM        newer_table  n
            JOIN        older_table     o  ON   n.cust_id     = o.cust_id
                                 AND     n.stamp_date     = o.stamp_date
                           AND     NVL ( n.gtp
                                    , 'X'
                                 )          != NVL ( o.gtp
                                                  , 'X'
                                              )
    
           )                 src
    ON     (    src.cust_id            = dst.cust_id
           AND  src.stamp_date     = dst.stamp_date
           AND  src.r_num            = 1
           )
    WHEN MATCHED THEN UPDATE
    SET    dst.gtp          = src.gtp
    ,      dst.update_date     = SYSDATE
    ;
    Since I can't test it, I can't be sure.
  • 2. Re: need help with Merge Statement
    Kodiak_Seattle Newbie
    Currently Being Moderated
    Question: Why is Merge faster - trying to learn ?

    And when you write the query, how do you format things ? is that the way you just learned or do you some tool that does it like that, also trying to learn.

    thank you - and let me test
  • 3. Re: need help with Merge Statement
    SomeoneElse Guru
    Currently Being Moderated
    Before you proceed, this line tells me you have a problem:
    AND ROWNUM = 1)
    If the combination of cust_id and stamp_id is not unique you could get the "unable to generate stable set of rows" error in a merge.

    Edited by: SomeoneElse on Jan 11, 2013 5:23 PM

    Ah, I see that Frank already anticipated that and used row_number.
  • 4. Re: need help with Merge Statement
    Kodiak_Seattle Newbie
    Currently Being Moderated
    Thank you SQL God.

    Old way = 30+ minutes.

    New way = 15 seconds.
  • 5. Re: need help with Merge Statement
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Kodiak_Seattle wrote:
    Question: Why is Merge faster - trying to learn ?
    Often (as in this case) an UPDATE statement needs to query another table to get the values, and then needs almost the exact same sub-query in the WHERE clause. MERGE can often combine those. This case is sort of an exception; since MERGE can't change any of the columns used in determining matches, we had to join the two tables in the USING clause.
    The subqueries in UPDATE statements are almost always correlated sub-queries, too, which means they (nominally) involve slow 1-row at a time processing. The optimizer may re-write them to use more efficient joins; I'm not an expert on that.
    And when you write the query, how do you format things ? is that the way you just learned or do you some tool that does it like that, also trying to learn.
    I do the formatting myself. I edit in Emacs, which has some nice indenting features.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points