This discussion is archived
13 Replies Latest reply: Mar 20, 2013 6:44 AM by BluShadow RSS

MERGE strange behavior

894045 Newbie
Currently Being Moderated
Hi all,

I have encountered a strange behavior from MERGE statement.

I have 2 tables,

table A (target table)
Key Value
1 A

table B (source table)
Key Value
1 1
1 2


Merge statement

MERGE INTO A USING B
ON (a.Key = b.Key)
WHEN MATCHED THEN
UPDATE SET
a.Value = decode(b.Value, '1', 'A', 'B')
;

So when I use merge statement, obvious it should return with error ORA-30926, right?

BUT, when I execute the merge statement for the first time, NO error is encountered. Error is only returned in the second run.
Also, if omit the decode and hardcode 'A' to it,

MERGE INTO A USING B
ON (a.Key = b.Key)
WHEN MATCHED THEN
UPDATE SET
a.Value = 'A'
;

No Error!

These is very frustrating! Does anyone have any idea why no error returned it the mentioned situations?

My system is Oracle 10.2.0.4.0 64-bit version.


Thanks!!!

Edited by: user13818537 on Oct 12, 2011 4:41 AM
  • 1. Re: MERGE strange behavior
    Dom Brooks Guru
    Currently Being Moderated
    But if you start with table a with entries of 1,'B' (the result of the UPDATE of the first MERGE) then you get the error immediately.

    Edited by: Dom Brooks on Oct 12, 2011 1:02 PM
    Re-evaluated reply
  • 2. Re: MERGE strange behavior
    €$ħ₪ Expert
    Currently Being Moderated
    encountering a duplicate ...use below one ...
    
    MERGE INTO key1 a
       USING (SELECT *
                FROM (SELECT b.KEY, b.VALUE,
                             ROW_NUMBER () OVER (PARTITION BY b.KEY ORDER BY b.VALUE DESC)
                                                                               rn
                        FROM key1 a, key2 b
                       WHERE a.KEY = b.KEY)
               WHERE rn = 1) b
       ON (a.KEY = b.KEY)
       WHEN MATCHED THEN
          UPDATE
             SET a.VALUE = 'A'
  • 3. Re: MERGE strange behavior
    AndreyN Pro
    Currently Being Moderated
    Seems as oracle optimizator trick.

    If table A contain only records with value = 'A' the below code will not raise error, as though oracle need not to do real update
    MERGE INTO A 
    USING B
    ON (a.Key = b.Key)
    WHEN MATCHED THEN
    UPDATE SET
     a.Value = 'A';
  • 4. Re: MERGE strange behavior
    894045 Newbie
    Currently Being Moderated
    That's true, but in real environment I cannot control what is in the DB and what is coming in, right?
    Maybe I should clarify one point. I know what's wrong with the data. What I am asking is that why the MERGE statement failed to report error? Should this be declared as an oracle bug?
  • 5. Re: MERGE strange behavior
    894045 Newbie
    Currently Being Moderated
    I agree with you. I also think that this is due to the optimizer, but before going to the optimizer, shouldn't oracle check for error first?
  • 6. Re: MERGE strange behavior
    Dom Brooks Guru
    Currently Being Moderated
    That's true, but in real environment I cannot control what is in the DB and what is coming in, right?
    Not sure I agree.
    You should not have duplicate values in the source data (b in this case).
    If there are duplicates then you need logic to deduplicate and establish which source row takes precedence
    The error should be expected behaviour.
    You should raise an SR for clarification and report back any feedback.
  • 7. Re: MERGE strange behavior
    AndreyN Pro
    Currently Being Moderated
    >
    I agree with you. I also think that this is due to the optimizer, but before going to the optimizer, shouldn't oracle check for error first?
    >

    For this error, I think it shouldn't. I think, oracle needs to analyze the real data to raise such an error, so it happens after optimizer has worked.

    I agree with Dom Brooks, you should raise an SR for clarification and report back any feedback.
  • 8. Re: MERGE strange behavior
    Dom Brooks Guru
    Currently Being Moderated
    Some other observations
    SQL> select * from a;
    
           KEY V
    ---------- -
             1 A
    
    SQL> select * from b;
    
           KEY V
    ---------- -
             1 1
             1 2
    
    SQL> merge into a
      2  using (select b.val
      3         ,      b.key
      4         from   b
      5         order by key asc) b
      6  on (a.key = b.key)
      7  when matched then 
      8   update
      9   set a.val = decode(b.val,'1','A','B');
    merge into a
               *
    ERROR at line 1:
    ORA-30926: unable to get a stable set of rows in the source tables
    
    
    SQL> rollback;
    
    Rollback complete.
    
    SQL> merge into a
      2  using (select b.val
      3         ,      b.key
      4         from   b
      5         order by key desc) b
      6  on (a.key = b.key)
      7  when matched then 
      8   update
      9   set a.val = decode(b.val,'1','A','B');
    merge into a
               *
    ERROR at line 1:
    ORA-30926: unable to get a stable set of rows in the source tables
    
    
    SQL> rollback;
    
    Rollback complete.
    
    SQL> merge into a
      2  using (select b.val
      3         ,      b.key
      4         from   b
      5         order by val asc) b
      6  on (a.key = b.key)
      7  when matched then 
      8   update
      9   set a.val = decode(b.val,'1','A','B');
    
    2 rows merged.
    
    SQL> rollback;
    
    Rollback complete.
    
    SQL> merge into a
      2  using (select b.val
      3         ,      b.key
      4         from   b
      5         order by val desc) b
      6  on (a.key = b.key)
      7  when matched then 
      8   update
      9   set a.val = decode(b.val,'1','A','B');
    merge into a
               *
    ERROR at line 1:
    ORA-30926: unable to get a stable set of rows in the source tables
    
    
    SQL> merge into a
      2  using (select b.val
      3         ,      b.key
      4         from   b) b
      5  on (a.key = b.key)
      6  when matched then 
      7   update
      8   set a.val = decode(b.val,'1','A','B')
      9  .
    SQL> rollback;
    
    Rollback complete.
    
    SQL> merge into a
      2  using (select b.val
      3         ,      b.key
      4         from   b) b
      5  on (a.key = b.key)
      6  when matched then 
      7   update
      8   set a.val = decode(b.val,'1','A','B');
    
    2 rows merged.
    
    SQL> rollback;
    
    Rollback complete.
  • 9. Re: MERGE strange behavior
    894045 Newbie
    Currently Being Moderated
    Thanks a lot Dom! I will see what I can do with the SR thing.
  • 10. Re: MERGE strange behavior
    Dom Brooks Guru
    Currently Being Moderated
    Exactly how the check ORA-30926 is implemented is possibly not documented.
    From the example below, it seems that it's not just a question of whether there are duplicates in the source data, but whether those duplicates actually result in an update to the same row, and not just an update statement that updates to the same value.
    SQL> select * from a;
    
           KEY V
    ---------- -
             1 A
    
    SQL> merge into a
      2  using (select b.val
      3         ,      b.key
      4         from   b
      5         order by b.val ASC) b  --<----- ORDER BY   
      6  on (a.key = b.key)
      7  when matched then 
      8   update
      9   set a.val = decode(b.val,'1','A','B');
    
    2 rows merged.
    
    SQL> select * from a;
    
           KEY V
    ---------- -
             1 B
    
    SQL> merge into a
      2  using (select b.val
      3         ,      b.key
      4         from   b
      5         order by b.val DESC) b  --<----- ORDER BY   
      6  on (a.key = b.key)
      7  when matched then 
      8   update
      9   set a.val = decode(b.val,'1','A','B');
    
    2 rows merged.
    
    SQL> select * from a;
    
           KEY V
    ---------- -
             1 A
    
    SQL> merge into a
      2  using (select b.val
      3         ,      b.key
      4         from   b
      5         order by b.val ASC) b  --<----- ORDER BY   
      6  on (a.key = b.key)
      7  when matched then 
      8   update
      9   set a.val = decode(b.val,'1','A','B');
    
    2 rows merged.
    
    SQL>  etc etc etc
    You might then say "well, what if we use a WHERE clause in the update":
    SQL> merge into a
      2  using (select b.val
      3         ,      b.key
      4         from   b
      5         ORDER BY b.val desc) b  --<----- ORDER BY   
      6  on (a.key = b.key)
      7  when matched then 
      8   update
      9   set a.val    = decode(b.val,'1','A','B')
     10   where a.val != decode(b.val,'1','A','B');
    
    1 row merged.
    
    SQL> select * from a;
    
           KEY V
    ---------- -
             1 B
    
    SQL> 
    But the where clause is determined by the read consistency of the select at the initial merge, so you only ever get one update, which might not be what you want.

    However, we're getting distracted.


    Bottom line (again) is that you should not have duplicates in the SOURCE.

    But I do think that the ORA-30926 should be consistent in both cases though - consistent as in always raised.

    Edited by: Dom Brooks on Oct 13, 2011 9:49 AM
  • 11. Re: MERGE strange behavior
    894045 Newbie
    Currently Being Moderated
    Thanks a lot Dom. I will bear the bottom line in mind (though it is not controllable this time as the data is fed by other system). I will update you guy once I have feedback from the SR.

    Thanks again for all the help!
  • 12. Re: MERGE strange behavior
    998070 Newbie
    Currently Being Moderated
    Hi ,

    Does someone had any feedback about this issue ??

    I'm having the same strange behavior here. I created a procedure with a query and have tested inserting two equal values on the Key column to raise the error. But on the first time there i run the Procedure it runs without error, apparently updating with the first row found. On the second time there i run the procedure then Oracle raises the error.

    Here's the oracle version:

    1     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    2     PL/SQL Release 11.2.0.3.0 - Production
    3     CORE     11.2.0.3.0     Production
    4     TNS for Linux: Version 11.2.0.3.0 - Production
    5     NLSRTL Version 11.2.0.3.0 - Production

    Thanks in advance.


    Best Regards.
    Renan Ribeiro
  • 13. Re: MERGE strange behavior
    BluShadow Guru Moderator
    Currently Being Moderated
    995067 wrote:
    Hi ,

    Does someone had any feedback about this issue ??

    I'm having the same strange behavior here. I created a procedure with a query and have tested inserting two equal values on the Key column to raise the error. But on the first time there i run the Procedure it runs without error, apparently updating with the first row found. On the second time there i run the procedure then Oracle raises the error.

    Here's the oracle version:

    1     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    2     PL/SQL Release 11.2.0.3.0 - Production
    3     CORE     11.2.0.3.0     Production
    4     TNS for Linux: Version 11.2.0.3.0 - Production
    5     NLSRTL Version 11.2.0.3.0 - Production

    Thanks in advance.


    Best Regards.
    Renan Ribeiro
    Please start your own question (reference back to this thread if you need), and post appropriate details to replicate your issue.

    Details of how to post a question here: {message:id=9360002}

Legend

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