MERGE and ORA-30926

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

Leave a Reply

Your email address will not be published. Required fields are marked *