what do you mean the "but not matched parts do not work?"


ops$tkyte@ORA9IR2> create table t ( x int, y int, z int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 1, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> merge into t
  2  using dual on ( x = 55 )
  3  when matched then
  4  update set z = z+1
  5  when not matched then insert (x,y,z) values ( 2, 2, 2 );
 
1 row merged.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2

<b>so there, x=55 did not exist, the one row was inserted..</b>
 
ops$tkyte@ORA9IR2> rollback;
 
Rollback complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values (55, 1, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> merge into t
  2  using dual on ( x = 55 )
  3  when matched then
  4  update set z = z+1
  5  when not matched then insert (x,y,z) values ( 2, 2, 2 );
 
1 row merged.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
         X          Y          Z
---------- ---------- ----------
         1          1          1
        55          1          2

<b>there x=55 did exist and got updated</b>


although i just tested in 9.0 instead of 9.2 and it did not update, perhaps that is it?  (if so, you'll want to file a tar with support for that)
 

Yes you "can"

Why you "would" escapes me :)  doesn't really seem to make sense (eg: when would you not have a "match" ?)

but anyway, simple test shows "yes"

ops$tkyte@ORA9IR2> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> merge into t t1
  2  using t t2
  3  on ( t1.x = t2.x )
  4  when matched then update set t1.y = t2.y
  5  when not matched then insert  values ( t2.x, t2.y );
 
1 row merged.
 

I believe you've made a mistake somewhere, show me the entire example:

ops$tkyte@ORA9IR2> CREATE TABLE STG_TEST
  2  (
  3    CENID                   VARCHAR2(22),
  4    BUISNESSUNITID              VARCHAR2(12),
  5    BRANDID                     VARCHAR2(12),
  6    BUISNESSUNITCUSTOMERID      VARCHAR2(20),
  7    MAILOPTINFLAG               VARCHAR2(1))
  8  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE MERG_TEST
  2  (
  3    CENID                   VARCHAR2(22),
  4    BUISNESSUNITID              VARCHAR2(12),
  5    BRANDID                     VARCHAR2(12),
  6    BUISNESSUNITCUSTOMERID      VARCHAR2(20) <b>UNIQUE,</b>
  7    MAILOPTINFLAG               VARCHAR2(1))
  8  /
 
Table created.
 
 
ops$tkyte@ORA9IR2> update ( select b.CENID
  2             from merg_test a, stg_test b
  3            where b.BUISNESSUNITCUSTOMERID = a.BUISNESSUNITCUSTOMERID )
  4  set cenid = '66666666666';
 
9 rows updated.
 
 

ops$tkyte@ORA9IR2> MERGE INTO emp@dblink n1
  2  USING emp e1
  3  ON ( n1.empno = e1.empno )
  4  WHEN MATCHED THEN UPDATE
  5      SET n1.ename    = e1.ename,
  6          n1.job      = e1.job,
  7          n1.mgr      = e1.mgr,
  8          n1.hiredate = e1.hiredate,
  9          n1.sal      = e1.sal,
 10          n1.comm     = e1.comm,
 11          n1.deptno   = e1.deptno
 12  WHEN NOT MATCHED THEN
 13  INSERT ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
 14  values ( e1.empno,
 15           e1.ename,
 16           e1.job,
 17           e1.mgr,
 18           e1.hiredate,
 19           e1.sal,
 20           e1.comm,
 21           e1.deptno )
 22  /
 
14 rows merged.


hows about an exact cut and paste and a santity check in place to verify that emp has an empno column 
 

that merge is "single statment" doesn't really impact the "This alone wins the day" thing.

concurrency control is a transactional thing -- under the covers, you know what merge does?

The statement:

<quote>
If two sessions runs this piece of code
</quote>

applies equally to "merge" as well as "update/insert".  Think about it! 

both merges will not find a row.
both merges will attemp to insert a new row.
only one of the merges will be successful!!!!!

The 'second' merge (if there is such a thing in a SMP machine even) cannot see the 'insert' of the first -- read consistency doesn't permit it.  Only one of the merges would 'win' (try it -- rather than just disagreeing with me -- 'show it to be better')..

consider:

ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t ( x int primary key, y int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> merge into t
  2  using ( select 1 a, 2 b from dual ) D
  3  on ( t.x = d.a )
  4  when not matched then insert (x,y) values (a,b)
  5  when matched then update set y = b;
 
1 row merged.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          merge into t
  5          using ( select 1 a, 2 b from dual ) D
  6          on ( t.x = d.a )
  7          when not matched then insert (x,y) values (a,b)
  8          when matched then update set y = b;
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

<b>in the real world -- that deadlock would have been "you are blocked", until the first merge commits (or rollsback) at which point the merge would either get ora-00001 (if you commit), or succeeds (if you rollback)</b>

The only way to avoid that would be:

insert
when dup val on index
  then update


but the performance implications of that are pretty bad if you get the dup val on index more than a couple of times....

or you could code:

update
if zero rows
then
    insert
    when dup val on index 
    then
        update


but merge -- merge will do exactly what you thought you would avoid -- definitely.




And from a performance perspective - I would still lean towards what I said:

ops$tkyte@ORA9IR2> create table t1 ( x int primary key, y int );
Table created.
 
ops$tkyte@ORA9IR2> insert into t1 select object_id, null from all_objects where rownum <= 5000;
5000 rows created.
 
ops$tkyte@ORA9IR2> commit;
Commit complete.
 
ops$tkyte@ORA9IR2> create table t2 ( x int primary key, y int );
Table created.
 
ops$tkyte@ORA9IR2> insert into t2 select * from t1 where rownum <= 5000;
5000 rows created.
 
ops$tkyte@ORA9IR2> commit;
Commit complete.
 
ops$tkyte@ORA9IR2> create or replace procedure p1
  2  as
  3  begin
  4      for C in (select x from t1)
  5      loop
  6          for i in 1 .. 2
  7          loop
  8              merge into t1
  9              using dual on ( t1.x = C.x )
 10              when matched then update set y = -C.x
 11              when not matched then insert (x,y) values ( c.x, -c.x );
 12
 13              c.x := -c.x;
 14          end loop;
 15      end loop;
 16  end;
 17  /
Procedure created.
 
ops$tkyte@ORA9IR2> create or replace procedure p2
  2  as
  3  begin
  4      for C in (select x from t2)
  5      loop
  6          for i in 1 .. 2
  7          loop
  8              update t2 set y = -c.x where x = c.x;
  9              if ( sql%rowcount = 0 )
 10              then
 11                  insert into t2 (x,y) values ( c.x, -c.x );
 12              end if;
 13
 14              c.x := -c.x;
 15          end loop;
 16      end loop;
 17  end;
 18  /
Procedure created.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p1
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p2
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_stop(500);
Run1 ran in 393 hsecs
Run2 ran in 205 hsecs
run 1 ran in 191.71% of the time

<b>the merge was slower when we are doing row/row processing here....</b>

 
Name                                  Run1        Run2        Diff
LATCH.simulator lru latch              679          20        -659
LATCH.simulator hash latch           5,193       1,458      -3,735
STAT...recursive calls              15,186      20,125       4,939
STAT...consistent gets - exami      15,155      20,112       4,957
STAT...shared hash latch upgra      10,009       5,050      -4,959
STAT...calls to kcmgas               5,033          53      -4,980
LATCH.shared pool                   10,222      15,211       4,989
STAT...execute count                10,021      15,018       4,997
STAT...free buffer requested         5,315         318      -4,997
LATCH.cache buffers lru chain        5,316         317      -4,999
STAT...switch current to new b       5,000           0      -5,000
STAT...table fetch by rowid          5,006           6      -5,000
LATCH.checkpoint queue latch         5,080          16      -5,064
LATCH.library cache                 20,328      30,310       9,982
LATCH.library cache pin             20,185      30,182       9,997
STAT...index fetch by key                6      10,006      10,000
STAT...table scans (short tabl      10,001           1     -10,000
STAT...table scan rows gotten       15,000       5,000     -10,000
STAT...table scan blocks gotte      15,006       5,006     -10,000
STAT...index scans kdiixs1          10,009           6     -10,003
STAT...calls to get snapshot s      33,101      18,096     -15,005
STAT...no work - consistent re      25,050      10,025     -15,025
STAT...buffer is not pinned co      25,036       5,036     -20,000
STAT...consistent gets              68,272      33,246     -35,026
STAT...session logical reads       113,770      78,734     -35,036
STAT...redo size                 5,384,376   5,446,584      62,208
LATCH.cache buffers chains         293,139     218,073     -75,066
 
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
388,142     323,452     -64,690    120.00%
 
PL/SQL procedure successfully completed.


<b>the merge used more resources when we go row by row (slow by slow).</b>

so, if you have a single row to update existing or insert if not there yet -- i'll stick with update/if zero rows/insert.


even in 10g, when dual is not really dual the table (but fast dual, the "function"), and you use using (select 1 from dual) -- to avoid the real dual table and use the "fast dual", the update/if zero/insert logic still bests merge (albeit by a smaller margin).  

So, row by row (slow by slow) -- i'll keep with my approach for right now.
 

 
ops$tkyte@ORA10GR1> create table t ( x int not null, y int );
 
Table created.
 
ops$tkyte@ORA10GR1> insert into t values ( 1, 1 );
 
1 row created.
 
ops$tkyte@ORA10GR1> create table t2 ( x int not null, y int );
 
Table created.
 
ops$tkyte@ORA10GR1> insert into t2 values ( 2, 2 );
 
1 row created.
 
ops$tkyte@ORA10GR1> exec dbms_stats.set_table_stats( user,'T' ,numrows=>10000, numblks=>10000);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR1> exec dbms_stats.set_table_stats( user,'T2',numrows=>1000, numblks=>10000);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> merge into t
  2  using t2 d
  3  on ( t.x = d.x )
  4  when not matched then insert (x,y) values ( d.x, d.y );
 
1 row merged.
<b>insert only, has same effect as:

insert into t select * from t2 where x not in ( select x from t );

(which leads me to question -- why? we didn't really need that one, but anyway....</b>
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> set autotrace on explain
ops$tkyte@ORA10GR1> merge into t
  2  using t2 d
  3  on ( t.x = d.x )
  4  when not matched then insert (x,y) values ( d.x, d.y )
  5  when matched then update set y = y+2;
 
1 row merged.

<b>that is 9i's only way... both insert and update</b>

 
Execution Plan
----------------------------------------------------------
   0      MERGE STATEMENT Optimizer=ALL_ROWS (Cost=4412 Card=10000 Bytes=650000)
   1    0   MERGE OF 'T'
   2    1     VIEW
   3    2       HASH JOIN (OUTER) (Cost=4412 Card=10000 Bytes=590000)
   4    3         TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=2206 Card=1000 Bytes=26000)
   5    3         TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=2206 Card=10000 Bytes=330000)
 
 
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> merge into t
  2  using t2 d
  3  on ( t.x = d.x )
  4  when matched then update set y = y+2;
 
1 row merged.
 
<b>but now you can just do the update portion (no more "key preserved table" issues...</b>
 
Execution Plan
----------------------------------------------------------
   0      MERGE STATEMENT Optimizer=ALL_ROWS (Cost=4412 Card=10000 Bytes=260000)
   1    0   MERGE OF 'T'
   2    1     VIEW
   3    2       HASH JOIN (Cost=4412 Card=10000 Bytes=590000)
   4    3         TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=2206 Card=1000 Bytes=26000)
   5    3         TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=2206 Card=10000 Bytes=330000)
 
 
 
ops$tkyte@ORA10GR1> set autotrace off
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select * from t;
 
         X          Y
---------- ----------
         1          1
         2          6
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> merge into t
  2  using t2 d
  3  on ( t.x = d.x )
  4  when matched then update set y = y+2
  5                    delete where y > 6;
 
1 row merged.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select * from t;
 
         X          Y
---------- ----------
         1          1
 
<b>that shows that it can delete after applying the update -- eg: data warehouse -- apply the incremental inventory counts -- when qty = 0, delete it)</b>

 

(why don't I get create tables and insert into's like I give you guys when I give examples??  so frustrating to have to generate that stuff from describes and selects, especially when the page you used to add this clearly at the top has:

If your followup requires a response that might include a query, you had better supply very very simple create tables and insert statements. I cannot create a table and populate it for each and every question. The SMALLEST create table possible (no tablespaces, no schema names, just like I do in my examples for you))

but i simplified your test case down to this:

ops$tkyte@ORA9IR2> create table merge_src ( key int, data int );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into merge_src values ( 1, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into merge_src values ( 1, 2 );
 
1 row created.
 
ops$tkyte@ORA9IR2> create table merge_dest ( key int, data int );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into merge_dest values ( 1, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from merge_src;
 
       KEY       DATA
---------- ----------
         1          1
         1          2
 
ops$tkyte@ORA9IR2> MERGE INTO MERGE_DEST D
  2  USING MERGE_SRC S
  3  ON (D.key = s.key)
  4  WHEN MATCHED THEN UPDATE SET data = s.data
  5  WHEN NOT MATCHED THEN INSERT (key,data) values (s.key,s.data);
USING MERGE_SRC S
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> rollback;
 
Rollback complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update merge_src set data = decode(rownum,1,2,1);
 
2 rows updated.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> select * from merge_src;
 
       KEY       DATA
---------- ----------
         1          2
         1          1
 
ops$tkyte@ORA9IR2> MERGE INTO MERGE_DEST D
  2  USING MERGE_SRC S
  3  ON (D.key = s.key)
  4  WHEN MATCHED THEN UPDATE SET data = s.data
  5  WHEN NOT MATCHED THEN INSERT (key,data) values (s.key,s.data);
 
2 rows merged.


It appears that if the update would not really update the row (not change the values) it "skips" it conceptually -- so, if we try to update DATA in the dest table to itself first - it says "hey, nothing to do, skip" (conceptually).  Later it gets the new value and says "these are different, update"

In the first case above, the data was processed in such a way that it changed Data from 1 to 2 in dest and then tried to change 2 to 1 -- that failed.

when I reversed the order of the "data" in the src table, it tried to change 1 to 1, did nothing in effect and then changed 1 to 2, successfully (but erroneously)


It should have failed both times.  I'll file a bug (but it might not be a bug -- i'll leave that for them to decide.  I see an existing closed bug that leads me to believe that they will say "deterministic behavior is observed here even if one fails and the other succeeds -- the one that succeeds always gets the same answer and the other one just doesn't "work""

so the merge is deterministic in its processing -- just the inclusion of the duplicates may prevent you from running the statement... 

give example.


ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table tt as select empno c001, ename c002 from scott.emp;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view contact
  2  as
  3  select empno cust_no, ename last_name from emp;

View created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> merge into contact c
  2  using (select * from tt where c001 in (select cust_no from contact)) t
  3  on (t.c001=c.cust_no)
  4  when matched then update set last_name=t.c002
  5  when not matched then insert(cust_no,last_name) values (t.c001,t.c002)
  6  /

14 rows merged.
 

In general it is more efficient to update and then try to insert.

Unless the row almost NEVER exists, you want to update then insert if not updated.


The insert would fail if the row exists.  In order to fail, it actually did the insert (generated redo, undo), then hits an error (likely duplicate key), then rollsback the row (undoes the changes to indexes, table) which generates yet more redo/undo, then queries the dictionary table cons$ to turn the constraint into a friendly name (runs more sql) and initiates the exception back to the client

then it updates.


Versus:

an update index unique scans by primary key, finds zero rows
then inserts.


Lets look at the two extremes - first run, all rows are inserted, second run, all rows are updated.

i'd rather have the penalty of the first run many times than the penalty implied by the second run sometimes!



ops$tkyte@ORA10GR1> drop table t1;

Table dropped.

ops$tkyte@ORA10GR1> drop table t2;

Table dropped.

ops$tkyte@ORA10GR1> drop table t3;

Table dropped.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> create table t1 ( x int primary key, y int, z int );

Table created.

ops$tkyte@ORA10GR1> create index t1_idx1 on t1(y);

Index created.

ops$tkyte@ORA10GR1> create index t1_idx2 on t1(z);

Index created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> create table t2 ( x int primary key, y int, z int );

Table created.

ops$tkyte@ORA10GR1> create index t2_idx1 on t2(y);

Index created.

ops$tkyte@ORA10GR1> create index t2_idx2 on t2(z);

Index created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> create table t3 as select object_id from all_objects ;

Table created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> begin
  2      for c in ( select * from t3 )
  3      loop
  4      begin
  5          insert into t1 values ( c.object_id, c.object_id, c.object_id );
  6      exception
  7          when dup_val_on_index
  8          then
  9                  update t1 set y = c.object_id, z = c.object_id where x = c.object_id;
 10      end;
 11      end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> begin
  2      for c in ( select * from t3 )
  3      loop
  4          update t2 set y = c.object_id, z = c.object_id where x = c.object_id;
  5          if ( sql%rowcount = 0 )
  6          then
  7                  insert into t2 values ( c.object_id, c.object_id, c.object_id );
  8          end if;
  9      end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> exec runstats_pkg.rs_stop(50000)
Run1 ran in 1780 hsecs
Run2 ran in 2273 hsecs
run 1 ran in 78.31% of the time

Name                                  Run1        Run2        Diff
STAT...session pga memory                0      65,536      65,536
STAT...session logical reads       353,074     447,856      94,782
STAT...consistent gets               3,622      98,404      94,782
STAT...consistent gets from ca       3,622      98,404      94,782
LATCH.library cache pin             96,750     192,498      95,748
LATCH.library cache                 97,593     193,603      96,010
LATCH.cache buffers chains       1,455,133   1,599,054     143,921

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
1,778,953   2,172,394     393,441     81.89%

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> begin
  2      for c in ( select * from t3 )
  3      loop
  4      begin
  5          insert into t1 values ( c.object_id, c.object_id, c.object_id );
  6      exception
  7          when dup_val_on_index
  8          then
  9                  update t1 set y = c.object_id, z = c.object_id where x = c.object_id;
 10      end;
 11      end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> begin
  2      for c in ( select * from t3 )
  3      loop
  4          update t2 set y = c.object_id, z = c.object_id where x = c.object_id;
  5          if ( sql%rowcount = 0 )
  6          then
  7                  insert into t2 values ( c.object_id, c.object_id, c.object_id );
  8          end if;
  9      end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> exec runstats_pkg.rs_stop(50000)
Run1 ran in 7059 hsecs
Run2 ran in 727 hsecs
run 1 ran in 970.98% of the time

Name                                  Run1        Run2        Diff
STAT...session pga memory           65,536           0     -65,536
STAT...table fetch by rowid         95,416           0     -95,416
STAT...execute count               143,131      47,714     -95,417
STAT...redo entries                143,140      47,723     -95,417
LATCH.session allocation            95,462           0     -95,462
LATCH.SQL memory manager worka      97,028         138     -96,890
STAT...table scan blocks gotte     143,676         550    -143,126
STAT...calls to get snapshot s     190,842      47,713    -143,129
LATCH.In memory undo latch         143,140           0    -143,140
STAT...consistent gets - exami     238,546      47,715    -190,831
STAT...buffer is not pinned co     190,832           0    -190,832
STAT...no work - consistent re     239,092      48,257    -190,835
LATCH.library cache lock           191,164          32    -191,132
STAT...db block changes            287,357      96,016    -191,341
LATCH.shared pool                  286,722      47,773    -238,949
LATCH.library cache pin            477,616      95,472    -382,144
LATCH.row cache objects            429,585          12    -429,573
STAT...recursive calls             525,283      48,191    -477,092
STAT...consistent gets from ca     620,773      95,981    -524,792
STAT...consistent gets             620,773      95,981    -524,792
STAT...db block gets from cach     622,394      48,869    -573,525
STAT...db block gets               622,394      48,869    -573,525
LATCH.library cache                716,511      95,524    -620,987
STAT...session logical reads     1,243,167     144,850  -1,098,317
LATCH.cache buffers chains       2,780,018     387,769  -2,392,249
STAT...table scan rows gotten    3,461,115     360,085  -3,101,030
STAT...undo change vector size   7,789,004   4,544,836  -3,244,168
STAT...redo size                32,253,444  12,562,056 -19,691,388

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
5,422,938     645,024  -4,777,914    840.73%

PL/SQL procedure successfully completed.
 

something like that - yes.

but, it would be more like:


for x in ( select * from source )
loop
    if record exists
    then
        if record hasn't been modified by us yet
        then
              update it
        else
              fail
        end if
    else
        insert it
    end if
end loop




ops$tkyte@ORA10GR1> create table t1 ( x int, y int );

Table created.

ops$tkyte@ORA10GR1> create table t2 ( x int, y int );

Table created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> insert into t1 values ( 1, 3 );

1 row created.

ops$tkyte@ORA10GR1> insert into t1 values ( 2, 1 );

1 row created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> merge into t2 using t1
  2  on (t1.x = t2.x)
  3  when matched then update set y = t1.y
  4  when not matched then insert (x,y) values (t1.x,t1.y);

2 rows merged.

ops$tkyte@ORA10GR1> /

2 rows merged.

ops$tkyte@ORA10GR1> insert into t1 values ( 1, 1 );

1 row created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> merge into t2 using t1
  2  on (t1.x = t2.x)
  3  when matched then update set y = t1.y
  4  when not matched then insert (x,y) values (t1.x,t1.y);
merge into t2 using t1
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
 

the "using" rowsource's "join key" <b>should</b> be unique, yes.  However, there are times when it need not be - but you should definitely avoid it:

ops$tkyte@ORA9IR2> create table t1 ( x int, y int, z int, a int );

Table created.

ops$tkyte@ORA9IR2> create table t2 ( x int, y int, z int, a int );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( 1, 1, 1, 0 );

1 row created.

ops$tkyte@ORA9IR2> insert into t2 values ( 1, 1, 1, 1 );

1 row created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> merge into t1 using t2
  2  on (t1.x = t2.x and t1.y = t2.y and t1.z = t2.z)
  3  when matched then update set a = t2.a
  4  when not matched then insert (x,y,z,a) values ( t2.x,t2.y,t2.z,t2.a );

1 row merged.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 1, 1, 1 );

1 row created.

ops$tkyte@ORA9IR2> merge into t1 using t2
  2  on (t1.x = t2.x and t1.y = t2.y and t1.z = t2.z)
  3  when matched then update set a = t2.a
  4  when not matched then insert (x,y,z,a) values ( t2.x,t2.y,t2.z,t2.a );

2 rows merged.


<b>Here, the second merge (update phase) worked because the values didn't actually change, didn't conflict....</b>

that should be avoided for logical reasons.


 

ops$tkyte@ORA10GR2> MERGE INTO target T
  2   USING ( select *
  3             from (select row_number()
                 over (partition by primary_key order by row_number desc) rn,
  5                          source.*
  6                     from source)
  7            where rn = 1 ) S
  8  on ( T.primary_key = S.primary_key )
  9  when matched then update
 10     set t.payload = s.payload
 11  when not matched then insert
 12   ( t.primary_key, t.payload )
 13   values
 14   ( S.primary_key, S.payload )
 15  /

5 rows merged.
 

[tkyte@dellpe ~]$ oerr ora 30926
30926, 00000, "unable to get a stable set of rows in the source tables"
// *Cause:  A stable set of rows could not be got because of large dml
//          activity or a non-deterministic where clause.
// *Action: Remove any non-deterministic where clauses and reissue the dml.
[tkyte@dellpe ~]$


this usually happens when  you try to update the same row twice.

You need a very very very simple and small example to see this.


ops$tkyte@ORA10GR2> create table t1 ( x int, y int );

Table created.

ops$tkyte@ORA10GR2> create table t2 ( x int, y int );

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> insert into t1 values ( 1, null );

1 row created.

ops$tkyte@ORA10GR2> insert into t2 values ( 1, 1 );

1 row created.

ops$tkyte@ORA10GR2> insert into t2 values ( 1, 2 );

1 row created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> merge into t1 using t2 on (t1.x = t2.x)
  2  when matched then update set y = t2.y;
merge into t1 using t2 on (t1.x = t2.x)
                    *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables


Should T2.Y be 1 or 2 after we are done?  Oracle is saying "I am not going to make that decision for you!!!"


So, I would assume that in  your really large example (which I did not really read through to be honest), one of your USING sets returns "more than a single record" for your ON join key and we don't know which record to use.