Oracle ошибка ora 30926

As someone explained earlier, probably your MERGE statement tries to update the same row more than once and that does not work (could cause ambiguity).

Here is one simple example. MERGE that tries to mark some products as found when matching the given search patterns:

CREATE TABLE patterns(search_pattern VARCHAR2(20));
INSERT INTO patterns(search_pattern) VALUES('Basic%');
INSERT INTO patterns(search_pattern) VALUES('%thing');

CREATE TABLE products (id NUMBER,name VARCHAR2(20),found NUMBER);
INSERT INTO products(id,name,found) VALUES(1,'Basic instinct',0);
INSERT INTO products(id,name,found) VALUES(2,'Basic thing',0);
INSERT INTO products(id,name,found) VALUES(3,'Super thing',0);
INSERT INTO products(id,name,found) VALUES(4,'Hyper instinct',0);

MERGE INTO products p USING
(
   SELECT search_pattern FROM patterns
) o
ON (p.name LIKE o.search_pattern)
WHEN MATCHED THEN UPDATE SET p.found=1;

SELECT * FROM products;

If patterns table contains Basic% and Super% patterns then MERGE works and first three products will be updated (found). But if patterns table contains Basic% and %thing search patterns, then MERGE does NOT work because it will try to update second product twice and this causes the problem. MERGE does not work if some records should be updated more than once. Probably you ask why not update twice!?

Here first update 1 and second update 1 are the same value but only by accident. Now look at this scenario:

CREATE TABLE patterns(code CHAR(1),search_pattern VARCHAR2(20));
INSERT INTO patterns(code,search_pattern) VALUES('B','Basic%');
INSERT INTO patterns(code,search_pattern) VALUES('T','%thing');

CREATE TABLE products (id NUMBER,name VARCHAR2(20),found CHAR(1));
INSERT INTO products(id,name,found) VALUES(1,'Basic instinct',NULL);
INSERT INTO products(id,name,found) VALUES(2,'Basic thing',NULL);
INSERT INTO products(id,name,found) VALUES(3,'Super thing',NULL);
INSERT INTO products(id,name,found) VALUES(4,'Hyper instinct',NULL);

MERGE INTO products p USING
(
   SELECT code,search_pattern FROM patterns
) s
ON (p.name LIKE s.search_pattern)
WHEN MATCHED THEN UPDATE SET p.found=s.code;

SELECT * FROM products;

Now first product name matches Basic% pattern and it will be updated with code B but second product matched both patterns and cannot be updated with both codes B and T in the same time (ambiguity)!
That’s why DB engine complaints. Don’t blame it! It knows what it is doing!

If you’ve ever used MERGE, then it is quite probable that you are familiar with ORA-30926 – unable to get a stable set of rows in the source tables. You have also probably noticed, that in case of hitting this error, your query takes longer. Three times longer, because the whole query is executed three times, as following tests show.

I already wrote about key-preserved tables and hitting the ORA-30926 or ORA-01779 in my post about BYPASS_UJVC hint.

There are several possible reasons for this error. The most common is probably following.

According to the documentation, Merge is a deterministic statement. It means you cannot update the same row of the target table multiple times in the same MERGE statement. If a table or a query in USING clause has duplicates with regard to the keys used in ON clause, and those records will match, thus are going to be updated multiple times, then you’ll get the ORA-30926 – unable to get a stable set of rows in the source tables. Let’s show this on the following test case:

CREATE TABLE t_source (id NUMBER, val NUMBER); 
CREATE TABLE t_target (id NUMBER, val NUMBER); 
INSERT INTO t_source VALUES (1,10); 
INSERT INTO t_source VALUES (2,10); 
INSERT INTO t_source VALUES (3,10); 
INSERT INTO t_target VALUES (1,1); 
INSERT INTO t_target VALUES (2,1); 
COMMIT; 

Now, we execute a MERGE statement, merging records from T_SOURCE into T_TARGET. We await no difficulties here, because ID in T_SOURCE is unique.

ALTER SESSION SET STATISTICS_LEVEL = ALL;

COLUMN SQL_ID NEW_VAL SQL_ID
SET PAGESIZE 0
SET LINESIZE 300

-- First Merge without an error

MERGE /*+ gather_plan_statistics */
/* MERGE_TEST_1 */
INTO t_target t
USING t_source q
ON (q.id = t.id)
WHEN MATCHED THEN UPDATE SET  t.val = t.val + q.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val);

ROLLBACK;

SELECT sql_id from v$sql vs
WHERE  VS.SQL_TEXT LIKE '%MERGE_TEST_1%'
AND    VS.SQL_TEXT NOT LIKE '%v$sql%';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', format=>'IOSTATS LAST'));

...

Plan hash value: 445881280
-------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers  |
-------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |          |      1 |        |      0 |00:00:00.01 |      22  |
|   1 |  MERGE               | T_TARGET |      1 |        |      0 |00:00:00.01 |      22  |
|   2 |   VIEW               |          |      1 |        |      3 |00:00:00.01 |      14  |
|*  3 |    HASH JOIN OUTER   |          |      1 |      3 |      3 |00:00:00.01 |      14  |
|   4 |     TABLE ACCESS FULL| T_SOURCE |      1 |      3 |      3 |00:00:00.01 |       7  |
|   5 |     TABLE ACCESS FULL| T_TARGET |      1 |      2 |      2 |00:00:00.01 |       7  |
-------------------------------------------------------------------------------------------

Note that all steps of this execution plan were executed only once (Starts=1). Actual rows are also feasible: join three rows in T_SOURCE with two rows in T_TARGET.

Now we’ll create a duplicate row in T_SOURCE and repeat our exercise:

-- Now creating duplicate record in the source table which leads to ORA-30926
INSERT INTO t_source (id, val) VALUES  (2,20);

COMMIT;

MERGE /*+ gather_plan_statistics */
/* MERGE_TEST_2 */
INTO t_target t
USING t_source q
ON (q.id = t.id)
WHEN MATCHED THEN UPDATE SET  t.val = t.val + q.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val);

SELECT sql_id from v$sql vs
WHERE  VS.SQL_TEXT LIKE '%MERGE_TEST_2%'
AND    VS.SQL_TEXT NOT LIKE '%v$sql%';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', format=>'IOSTATS LAST'));

Plan hash value: 445881280

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time | Buffers |
-------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |          |      3 |        |      0 |00:00:00.01 |      36 |
|   1 |  MERGE               | T_TARGET |      3 |        |      0 |00:00:00.01 |      36 |
|   2 |   VIEW               |          |      3 |        |     10 |00:00:00.01 |      40 |
|*  3 |    HASH JOIN OUTER   |          |      3 |      4 |     10 |00:00:00.01 |      40 |
|   4 |     TABLE ACCESS FULL| T_SOURCE |      3 |      4 |     12 |00:00:00.01 |      21 |
|   5 |     TABLE ACCESS FULL| T_TARGET |      3 |      2 |      6 |00:00:00.01 |      19 |
-------------------------------------------------------------------------------------------

What’s going on here? Note, how all steps being executed three times (starts=3). A-Rows for T_TARGET is six (2 rows x 3 times) and for T_SOURCE is now 12 (now 4 rows x 3 times).
Is this a flaw of display_cursor or is Oracle in fact doing all the stuff three times?

Maybe we can prove it, creating some triggers:

SET SERVEROUTPUT ON

CREATE OR REPLACE TRIGGER t_merge_test
BEFORE INSERT OR UPDATE ON t_target FOR EACH ROW
BEGIN
IF INSERTING THEN
  DBMS_OUTPUT.PUT_LINE('Inserting ID='||:new.id||' new val='||:new.val);
ELSIF UPDATING THEN
  DBMS_OUTPUT.PUT_LINE('Updating ID='||:new.id||' new val='||:new.val||', old val='||:old.val);
END IF;
END;
/

CREATE OR REPLACE FUNCTION test_func (id NUMBER, val NUMBER) RETURN NUMBER IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Querying ID '||id||' val '||val);
  RETURN val;
END;
/

MERGE /*+ gather_plan_statistics */
/* MERGE_TEST_3 */
INTO t_target t
USING (SELECT id, test_func(id, val) val FROM t_source) q
ON (q.id = t.id)
WHEN MATCHED THEN UPDATE SET  t.val = t.val + q.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES(q.id, q.val);

Querying ID 1 val 10
Updating ID=1 new val=11, old val=1
Querying ID 2 val 20
Updating ID=2 new val=21, old val=1
Querying ID 2 val 10
Updating ID=2 new val=11, old val=1
Querying ID 2 val 10
Querying ID 1 val 10
Querying ID 2 val 20
Querying ID 2 val 10
Querying ID 3 val 10
Querying ID 1 val 10
Updating ID=1 new val=11, old val=1
Querying ID 2 val 20
Updating ID=2 new val=21, old val=1
Querying ID 2 val 10
Updating ID=2 new val=11, old val=1

SELECT DISTINCT FIRST_VALUE(sql_id) OVER (ORDER BY VS.LAST_ACTIVE_TIME DESC) sql_id
FROM   v$sql vs
WHERE  VS.SQL_TEXT LIKE '%MERGE_TEST_3%'
AND    VS.SQL_TEXT NOT LIKE '%v$sql%';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&sql_id', format=>'IOSTATS LAST'));

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |          |      3 |        |      0 |00:00:00.07 |     645 |     11 |
|   1 |  MERGE               | T_TARGET |      3 |        |      0 |00:00:00.07 |     645 |     11 |
|   2 |   VIEW               |          |      3 |        |     10 |00:00:00.07 |     646 |     11 |
|*  3 |    HASH JOIN OUTER   |          |      3 |      4 |     10 |00:00:00.01 |      40 |      0 |
|   4 |     TABLE ACCESS FULL| T_SOURCE |      3 |      4 |     12 |00:00:00.01 |      21 |      0 |
|   5 |     TABLE ACCESS FULL| T_TARGET |      3 |      2 |      6 |00:00:00.01 |      19 |      0 |
----------------------------------------------------------------------------------------------------

From the output of our function and trigger one could clearly see, that we were actually querying and updating until we found a row which would be updated twice, then re-querying all rows, then start updating again and finally throwing an error. Last year I’ve had an opportunity to ask Jonathan Lewis at the CBO Days 2014 in Zurich, an event organized by Trivadis, the company I work for. Of course, his answer showed me the right direction to think. We are dealing with statement restarts here. In this case it is even possible with only one session. I’ll show this in my next post.

There are also a couple of bugs related to ORA-30926, see MOS How to Troubleshoot ORA-30926 Errors? (Doc ID 471956.1)

Related Posts

I was on call last week and I got a ticket about a batch job that was failing on a MERGE statement with an ORA-30926 error. Our support team worked around it by deleting some duplicate rows in a table that was feeding into the MERGE. This week I wanted to go back and try to understand what would cause ORA-30926 errors on MERGE statements.

I read through some blog posts and Oracle support documents relating to ORA-30926 and merge. Then I tried building some simple test scripts to see when you get ORA-30926 on a MERGE. At the end of my search I came back to this simple statement from the 18c SQL Language Reference manual’s description of the MERGE statement:

MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.

I was puzzled by the term “deterministic” but “cannot update the same row…multiple times” seems simple enough. I created test scripts to show whether you can update the same row multiple times with a MERGE: zip

Here are the two test tables:

create table target (a number,b number);

create table source (a number,b number);

Here is data and a MERGE statement that causes the error:

SQL> insert into target values (1,1);
SQL> insert into source values (1,2);
SQL> insert into source values (1,3);

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b;
using source
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables 

This seems very simple. We join the source and target tables on column A with value 1. The merge statement tries to update column B on the target table row twice. It tries to set it to 2 and then to 3. I guess this is where the term “deterministic” comes in. If the merge statement updated B to 2 and 3 which would it do first and which second?

The example above with values 2 and 3 for B makes good sense but I saw some odd behavior when I used source rows with 1 and 2 for the B values of the two rows. With B values of 1 and 2 in this case the MERGE does not get an error:

SQL> insert into target values (1,1);
SQL> insert into source values (1,1);
SQL> insert into source values (1,2);

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b;

2 rows merged.

SQL> select * from target;

         A          B                                                           
---------- ----------                                                           
         1          2                                                           

This seems to contradict the idea that you cannot update the same row multiple times with a MERGE. In this case it seems like B is updated twice, once to 1 which is what it already was set to and then to 2. I guess this works because B was already set to 1 so the update of B to the same value does not count as an update. It seems like only one update took place and then B ends up set to 2.

This example does not work with a slightly different MERGE statement on Oracle 12.1 or earlier:

SQL> select banner from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b
  6  when not matched then
  7  insert values (source.a,source.b);
using source
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables 

SQL> select * from source;

         A          B                                                           
---------- ----------                                                           
         1          1                                                           
         1          2                                                           

SQL> 
SQL> select * from target;

         A          B                                                           
---------- ----------                                                           
         1          1                                                           

All that I can say based on these two tests is that sometimes an update of a column to the same value counts as an update and sometimes it does not. The preceding example works on 12.2:

SQL> select banner from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production    

SQL> merge into target
  2  using source
  3  on (target.a = source.a)
  4  when matched then
  5  update set target.b=source.b
  6  when not matched then
  7  insert values (source.a,source.b);

2 rows merged.

SQL> select * from source;

         A          B                                                           
---------- ----------                                                           
         1          1                                                           
         1          2                                                           

SQL> 
SQL> select * from target;

         A          B                                                           
---------- ----------                                                           
         1          2                                                           

It all seemed so simple when I read the documentation. Maybe instead of saying:

You cannot update the same row of the target table multiple times in the same MERGE statement.

Instead it should say:

You may not be able to update the same row of the target table multiple times in the same MERGE statement.

We should not count on being able to update the same row multiple times with a single MERGE statement, but clearly there are some cases in which we can. If you are like me and you get paged on a job that fails with ORA-30926 on a MERGE, it makes sense to get rid of the multiple updates on a single row because you most likely have hit a case where it is not allowed.

Bobby