This discussion is archived
6 Replies Latest reply: Oct 3, 2013 4:26 AM by Purvesh K RSS

ORA-30926 - Merge statement

Purvesh K Guru
Currently Being Moderated

Hello All,

 

I am facing an issue with failure of merge statement due to duplicates in Source table. What I want to understand is, in a merge statement, if the Update statement in when matched clause contains WHERE predicates, are they pushed up to filter the rows in the ON clause?

 

In order to understand, I tried to frame a test case that will model the way my actual scenario is:

 

create table tab_src (col1 number, col2 number, col3 number, col4 varchar2(5));
create table tab_des (col1 number, col2 number, col3 number, col4 varchar2(5));

 

insert into tab_src values (1, 1, 1, 'AAA');
insert into tab_src values (1, 1, 0, 'AAB');

insert into tab_des values (1, 1, -1, null);

 

explain plan for
merge into tab_des d
using (
        select *
          from tab_src
         where col3 is not null
      ) s
on (d.col1 = s.col1
    and d.col2 = s.col2
   )
when matched then
  update set col4 = s.col4
   where d.col3 is not null;

 

select *
  from table(dbms_xplan.display(null, null, 'ALL'));

Plan hash value: 3751341164

 

--------------------------------------------------------------------------------

| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | MERGE STATEMENT      |         |     1 |    60 |     7  (15)| 00:00:01 |

|   1 |  MERGE               | TAB_DES |       |       |            |          |

|   2 |   VIEW               |         |       |       |            |          |

|*  3 |    HASH JOIN         |         |     1 |    98 |     7  (15)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| TAB_DES |     1 |    55 |     3   (0)| 00:00:01 |

|*  5 |     TABLE ACCESS FULL| TAB_SRC |     2 |    86 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - MRG$1      

   3 - SEL$F5BB74E1

   4 - SEL$F5BB74E1 / D@SEL$1

   5 - SEL$F5BB74E1 / TAB_SRC@SEL$2

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("D"."COL1"="TAB_SRC"."COL1" AND

              "D"."COL2"="TAB_SRC"."COL2")

   5 - filter("COL3" IS NOT NULL)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[76], SYSDEF[32720]

   2 - "D"."COL3"[NUMBER,22], "S"."COL4"[VARCHAR2,5]

   3 - (#keys=2) "D"."COL1"[NUMBER,22], "TAB_SRC"."COL1"[NUMBER,22],

       "D"."COL2"[NUMBER,22], "TAB_SRC"."COL2"[NUMBER,22],

       "D".ROWID[ROWID,10], "D"."COL4"[VARCHAR2,5], "D"."COL3"[NUMBER,22],

       "TAB_SRC"."COL4"[VARCHAR2,5], "COL3"[NUMBER,22]

   4 - "D".ROWID[ROWID,10], "D"."COL1"[NUMBER,22],

       "D"."COL2"[NUMBER,22], "D"."COL3"[NUMBER,22], "D"."COL4"[VARCHAR2,5]

   5 - "TAB_SRC"."COL1"[NUMBER,22], "TAB_SRC"."COL2"[NUMBER,22],

       "COL3"[NUMBER,22], "TAB_SRC"."COL4"[VARCHAR2,5]

 

Note

-----

   - dynamic sampling used for this statement

 

The Predicate Information section, makes no reference to the COL3 column of Destination table. So would it be correct to conclude that the where predicates in UPDATE clause will play no part in identifying the data/rows from source table/query?

 

I am using oracle 10.2.0.5.

  • 1. Re: ORA-30926 - Merge statement
    BluShadow Guru Moderator
    Currently Being Moderated

    That's correct, the where clauses are applied as the update is being done, they will not form part of the ON condition that identifies the rows to merge.  Hence you need to ensure that your ON condition uniquely joins the rows to be merged and there are no duplicates.

  • 2. Re: ORA-30926 - Merge statement
    Hoek Guru
    Currently Being Moderated

    So would it be correct to conclude that the where predicates in UPDATE clause will play no part in identifying the data/rows from source table/query?

    Yes, in relation to your error, the USING part plays the part.

    In other words:

    The set you are "using" to merge into your table contains duplicates by the join key.

     

    You can read several explanations/demo's on asktom in this thread:

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5318183934935#tom65092417031250

  • 3. Re: ORA-30926 - Merge statement
    Purvesh K Guru
    Currently Being Moderated

    I did read this same thread you posted in reply to another question asked, today.

     

    Thanks it did help, but did not clear earlier if the where clauses are pushed to the ON condition.

  • 4. Re: ORA-30926 - Merge statement
    Purvesh K Guru
    Currently Being Moderated

    Thanks for confirming this Blu.

     

    I have another question, perhaps not a sane one to ask, shouldn't Explain plan consider the where clauses in the update? I think it would make the situation clearer.

  • 5. Re: ORA-30926 - Merge statement
    BluShadow Guru Moderator
    Currently Being Moderated

    Perhaps it should include it in the explain plan, but I guess Oracle either missed it or weighed up the differences between showing something that may mislead you into thinking the where clause was applied to the join of the merging records, compared with the where clause being applied to the update or insert statements of the merge.  At the end of the day the explain plan is showing the plan for the merge itself, not for whether that results in an update or insert (or even delete if you include it), so as the where clauses apply to those sub-parts of the merge, I can see how it may confuse to show those on the explain plan.  I guess a future enhancement to the explain plan could include a special merge predicates section as well.

     

    Maybe the best way to look at it, for merge statements, is that the explain plan shows you how the fetchings of the data is done, including the joins required to implement that fetch, whereas the where clauses you're referring to are part of the conditions for updating, inserting or deleting

  • 6. Re: ORA-30926 - Merge statement
    Purvesh K Guru
    Currently Being Moderated

    Thanks Blu. The explanation is excellent andit clears my question.

Legend

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