MERGE and ORA-30926

Just a quick blog post on MERGE and the “unable to get a stable set of rows” error that often bamboozles people. This is actually just the script output from a pre-existing YouTube video (see below) that I’ve already done on this topic, but I had a few requests for the SQL example end-to-end, so here it is.

Imagine the AskTOM team had a simple table defining the two core members, Chris Saxon and myself. But in the style of my true Aussie laziness, I was very slack about checking the quality of the data I inserted.


SQL> create table oracle_team (
  2      pk number primary key,
  3      first_name varchar2(10),
  4      last_name varchar2(10)
  5  );

Table created.

SQL>
SQL> insert into oracle_team (pk, first_name, last_name) values (1, 'Connor', 'Macdonald');

1 row created.

SQL> insert into oracle_team (pk, first_name, last_name) values (3, 'kris', 'saxon');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from oracle_team;

        PK FIRST_NAME LAST_NAME
---------- ---------- ----------
         1 Connor     Macdonald
         3 kris       saxon

2 rows selected.

You can see that the data is garbage. Both of our names are wrong so they need fixing. So I build a table called FIXES which lets people “queue up” fix requests to the table. I’ll add the 2 obvious fixes to that table.


SQL> create table fixes (
  2      team_pk number,
  3      first_name varchar2(10),
  4      last_name  varchar2(10),
  5      requested  date
  6  );

Table created.

SQL> insert into fixes values (1, 'Connor', 'McDonald',sysdate);

1 row created.

SQL> insert into fixes values (3, 'Chris', 'Saxon',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from fixes;

   TEAM_PK FIRST_NAME LAST_NAME  APPLIED
---------- ---------- ---------- ---------
         1 Connor     McDonald   18-FEB-19
         3 Chris      Saxon      18-FEB-19

2 rows selected.

To apply those fixes to the table, a simple MERGE command is all I need. Notice that MERGE does not have to be a “full” merge (ie, update AND insert), you can pick and choose what elements you want. MERGE is very powerful and flexible in that regard.


SQL>
SQL>
SQL> merge into oracle_team target
  2  using (select team_pk, first_name, last_name
  3         from fixes
  4        ) source
  5  on (target.pk = source.team_pk)
  6  when matched then
  7  update set
  8      target.first_name = source.first_name,
  9      target.last_name = source.last_name
 10
SQL> pause

SQL> /

2 rows merged.

SQL>
SQL> select * from oracle_team;

        PK FIRST_NAME LAST_NAME
---------- ---------- ----------
         1 Connor     McDonald
         3 Chris      Saxon

2 rows selected.

So all looks well. Let me now show how what seems like a simple repeat of that operation can get us into trouble. I’ve dropped all the tables, so that I can recreate the demo from scratch.


SQL>
SQL> create table oracle_team (
  2      pk number primary key,
  3      first_name varchar2(10),
  4      last_name varchar2(10)
  5  );

Table created.

SQL>
SQL> insert into oracle_team (pk, first_name, last_name) values (1, 'Connor', 'Macdonald');

1 row created.

SQL> insert into oracle_team (pk, first_name, last_name) values (3, 'kris', 'saxon');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from oracle_team;

        PK FIRST_NAME LAST_NAME
---------- ---------- ----------
         1 Connor     Macdonald
         3 kris       saxon

2 rows selected.

SQL> create table fixes (
  2      team_pk number,
  3      first_name varchar2(10),
  4      last_name  varchar2(10),
  5      applied    date
  6  );

Table created.

This time we’ll assume that repeated fix requests have come in for a single AskTOM team member (PK=1). My first fix request was to the change the “Mac” to “Mc” in McDonald, but then I got all picky and realised that I’d like to have a capital “D” in McDonald. Fussy fussy fussy


SQL> insert into fixes values (1, 'Connor', 'Mcdonald',sysdate-1);

1 row created.

SQL> insert into fixes values (1, 'Connor', 'McDonald',sysdate);

1 row created.

SQL> insert into fixes values (3, 'Chris', 'Saxon',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from fixes;

   TEAM_PK FIRST_NAME LAST_NAME  APPLIED
---------- ---------- ---------- ---------
         1 Connor     Mcdonald   17-FEB-19
         1 Connor     McDonald   18-FEB-19
         3 Chris      Saxon      18-FEB-19

3 rows selected.

SQL>

Look what happens now when I re-attempt the MERGE.


SQL>
SQL>
SQL> merge into oracle_team target
  2  using (select team_pk, first_name, last_name
  3         from fixes
  4        ) source
  5  on (target.pk = source.team_pk)
  6  when matched then
  7  update set
  8      target.first_name = source.first_name,
  9      target.last_name = source.last_name
 10
SQL> pause

SQL> /
merge into oracle_team target
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

Conceptually this example (hopefully) makes it clear why the error occurred. Depending on which FIXES row for my row in the target table (PK=1) the database sees first, the end result of the MERGE could be different. And we really can’t allow that because it means the results are pseudo-random.

So how can we fix these things? We need a stable set of rows in the sense that the MERGE results should never be questionable based on order in which we process the input set of rows. In the example above, we could probably make an assumption that the last fix request for a given primary key value is the one that should always take precedence. Hence the source set of fixes can be altered as below.


SQL>
SQL>
SQL> select *
  2  from
  3  (select team_pk,
  4          first_name,
  5          last_name,
  6          row_number() over ( partition by team_pk order by applied desc ) as r
  7   from fixes
  8  ) where r = 1;

   TEAM_PK FIRST_NAME LAST_NAME           R
---------- ---------- ---------- ----------
         1 Connor     McDonald            1
         3 Chris      Saxon               1

2 rows selected.

which then becomes an input into the MERGE command and the error is avoided.


SQL>
SQL> merge into oracle_team target
  2  using
  3  ( select *
  4    from
  5    (select team_pk,
  6            first_name,
  7            last_name,
  8            row_number() over ( partition by team_pk order by applied desc ) as r
  9     from fixes
 10    ) where r = 1
 11  ) source
 12  on (target.pk = source.team_pk)
 13  when matched then
 14  update set
 15      target.first_name = source.first_name,
 16      target.last_name = source.last_name
 17

SQL> /

2 rows merged.

SQL>
SQL> select * from oracle_team;

        PK FIRST_NAME LAST_NAME
---------- ---------- ----------
         1 Connor     McDonald
         3 Chris      Saxon

2 rows selected.

The video walking through this demo came from an Office Hours session, and you can watch it below

4 comments

Pingback: MERGE and ORA-8006 – Learning is not a spectator sport
Santosh Kumar Panigrahi says:

September 7, 2019 at 2:23 am

You are super.
I have one question Connor which is faster merge or delete .

1. Connor McDonald says:
  
  September 25, 2019 at 8:16 am
  
  Totally depends on the situation. Like all things, we test and get concrete results and then choose the appropriate tools accordingly.
  
  1. Shivani Gupta says:
    
    October 19, 2020 at 5:13 pm
    
    I am getting 2 records for rank 1 and getting “ORA-30926: unable to get a stable set of rows in the source tables”; Due business logic, I can not change columns in partition by clause. If I am using row_number(); I am able to get one record, not getting error and but query is getting slower because large number of records. Is there any other way to fix it without impacting performance.