Climbing up the oracle merge statement eventually


Wanted to write this post for ages… More than ever to record my findings and sample applications of this, in words, really really powerful sql statement with all its options and, well, probable pitfalls. Credits to be premetioned go to Tim @ oracle-base.com for the 9i introduction of merge (MERGE Statement) and the according 10g update (MERGE Statement Enhancements in Oracle Database 10g). I also used the latest docs @ Oracle being of 12c (MERGE), not revealing any newer functionality beyond 10g.

Ok so, what’s important to understand first? The basic idea? Well, from my point of view and generally spoken, merge always applies a source dataset to a target dataset by behalf of selecting from source and target via a join condition. No matter where the dataset comes from or goes to, a table or a view or whatever, one should quite simply always think of a source and a target selection in action. The basic pseudo-code statement may so far just look like so, executing the matched or not matched blocks when the target-source-join-conditions become true or false.

merge into __target__ using ( __source__ )
on ( __target-source-join-conditions__ )
when matched then
  update set col = val, ...
  delete where __target-delete-conditions__
when not matched then
  insert (col, ...) values (val, ...)
;

ORA-30926: unable to get a stable set of rows in the source tables

Having declared source and target one should furthermore always:

  1. regard the target selection as the driver of the statement execution and
  2. make shure that the source selection returns a single row for every update match.

While #1 is the preferred way of figuring out what happens in terms of consecutively taking pieces from both selections, comparing and merging, #2 implies some more logic to consider, especially interrelated to the ORA-30926: unable to get a stable set of rows in the source tables exception. ORA-30926 will be thrown when the source selection is not unique with regard to every row of the target selection. That is, a match might receive more than one row from source for a single row in target. Many people also phrase: the on ( __target-source-join-conditions__ ) must deliver a unique set of values for every joined column. In fact, only the source selection needs to be unique since it is applied to the target selection for every row consecutively. So rewriting the pseudo-code statement above with another pseudo-code implicit-cursor-loop will deliver the following:

for c1 in ( __target__ ) loop
  if (  __target-source-join-conditions__ ) then -- matched
    update set (col, ...) = (select val, ... from __source__ where __target-source-join-conditions__)
    if (  __target-updated__ ) then -- matched
      delete where __target-delete-conditions__
    end if;
  else -- not matched
    insert (col, ...) values (val, ...)
  end if;
end loop;

An example may use the on ( __target-source-join-conditions__ ) within an outer join for checkup:

drop table src;
drop table dst;

create table src as
  select 'A' as tp, 1 as id, 's-A-1' as cmt from dual union all
  select 'C' as tp, 1 as id, 's-C-1' as cmt from dual union all
  select 'D' as tp, 4 as id, 's-D-4' as cmt from dual;

create table dst as
  select 'A' as tp, 1 as id, 'd-A-1' as cmt from dual union all
  select 'B' as tp, 1 as id, 'd-B-1' as cmt from dual union all
  select 'B' as tp, 2 as id, 'd-B-2' as cmt from dual union all
  select 'B' as tp, 3 as id, 'd-B-3' as cmt from dual;

select s.rowid as s_rid, d.rowid as d_rid,
  count(*) over(partition by d.rowid) as uq_cnt,
  (case when(d.rowid is null) then 'INSERT' else 'UPDDEL' end) as act, s.*, d.*
from dst d, src s
  where d.id(+) = s.id
order by 1,2;

-- since a join on just dst/src.id takes two matching id=1 rows from src
--   any id=1 rows from dst may receive two updates which is not possible (uq_cnt > 1)
AAAfqLAAdAADT1DAAA	AAAfqMAAdAADT1LAAA	2	UPDDEL	A	1	s-A-1	A	1	d-A-1
AAAfqLAAdAADT1DAAA	AAAfqMAAdAADT1LAAB	2	UPDDEL	A	1	s-A-1	B	1	d-B-1
AAAfqLAAdAADT1DAAB	AAAfqMAAdAADT1LAAA	2	UPDDEL	C	1	s-C-1	A	1	d-A-1
AAAfqLAAdAADT1DAAB	AAAfqMAAdAADT1LAAB	2	UPDDEL	C	1	s-C-1	B	1	d-B-1
AAAfqLAAdAADT1DAAC		1	INSERT	D	4	s-D-4			

merge into dst d using src s on (d.id = s.id)
when matched then
  update set d.cmt = d.cmt || ', ' || s.cmt;
...
SQL-Fehler: ORA-30926: Stabile Zeilengruppe in den Quelltabellen kann nicht eingelesen werden
...

-- enshuring distinct column values from source, with regard to the on join
--   resolves the problem
delete from src where tp = 'C' and id = 1; 

select s.rowid as s_rid, d.rowid as d_rid,
  count(*) over(partition by d.rowid) as uq_cnt,
  (case when(d.rowid is null) then 'INSERT' else 'UPDDEL' end) as act, s.*, d.*
from dst d, src s
  where d.id(+) = s.id
order by 1,2;

AAAfqLAAdAADT1DAAA	AAAfqMAAdAADT1LAAA	1	UPDDEL	A	1	s-A-1	A	1	d-A-1
AAAfqLAAdAADT1DAAA	AAAfqMAAdAADT1LAAB	1	UPDDEL	A	1	s-A-1	B	1	d-B-1
AAAfqLAAdAADT1DAAC		1	INSERT	D	4	s-D-4			

merge into dst d using src s on (d.id = s.id)
when matched then
  update set d.cmt = d.cmt || ', ' || s.cmt;
2 Zeilen zusammengeführt.

select * from dst;

A	1	d-A-1, s-A-1
B	1	d-B-1, s-A-1
B	2	d-B-2
B	3	d-B-3

update and the delete statements within the matched block

Another fact to exemplify is the dependency of the update and the delete statements within the matched block. That is, any delete statement will only be executed when there has been a row update and a match beforehand, respectively. The following delete will never be reached because Oracle skips the matched block for any update with a sql%rowcount of 0:

merge into __target__ using ( __source__ )
on ( __target-source-join-conditions__ )
when matched then
  update set col = val, ... where 1=0
  delete where __target-delete-conditions__
;

Finally, iff the target-delete-conditions contains target columns being updated before, the target-delete-conditions regards the updated values for evaluation. Tim @ oracle-base.com recommends to use source columns as an alternate iff the old value is applicable and necessary in the target-delete-conditions. In other words, the target-delete-conditions also allow for source columns to be given.

Have fun, Peter

Advertisements

2 comments

    1. hi affleck, everything i “repeated” for completeness has been cited accordingly.
      please do appreciate that (moreover, since this not canon in the blogosphere).
      peter

      Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s