5 Replies Latest reply: Jan 11, 2013 7:48 PM by Frank Kulash RSS

    need help with Merge Statement

    Kodiak_Seattle
      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
          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
            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
              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
                Thank you SQL God.

                Old way = 30+ minutes.

                New way = 15 seconds.
                • 5. Re: need help with Merge Statement
                  Frank Kulash
                  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.