One of the topics I’ve found very interesting as I started blogging was the sometimes strange behavior of the MERGE statement, the reasons for ORA-30926 and what it has to do with write consistency. My first blog post about it was almost exactly ten years ago. Time for a follow up! Especially because there are some changes in Oracle 23ai.
If you don’t have time to read both posts from the past (the first and the second) here is the brief summary:
- ORA-30926 is thrown if the source data for the MERGE is not unique with respect to the ON-condition, thus attempting to update the same row more than once
- At the same time Oracle treats the condition to throw ORA-30926 as the reason to attempt a DML statement restart – this behavior seemed to be a bug. This restart caused by the duplicates will be, of course, unsuccessful because those duplicates are still there during the restart; but it means a significantly increased amount of work needs to be done before the ORA-30926 error is thrown anyway.
- The behavior of the MERGE statement in context of write consistency and DML restarts is different from that of the UPDATE. Merge is not tracking columns in the ON-clause, but instead it is tracking columns in the SET-clause
New Error Messages
One of the many enhancements in Oracle 23ai are the improved error messages. In July 2023 Gerald Venzl announced the new error help portal. Many of the error messages that used to be a bit cryptic are now easier to understand and also provide additional information. It was long overdue and good job, Oracle!
How about ORA-30926? Do we also have a better error message? Yes!
Just compare this one, which we had since Oracle 9i (Figure 1) with the new error message (Figure 2):
Figure 1: ORA-30926 prior to 23ai
Figure 2: ORA-30926 from 23ai onwards
When reading the old message, it is noticeable that the duplicates in the source leading to multiple updates of the same row in the target were not mentioned at all. It seems that in the past Oracle tried to cover two different causes for an error situation with just one action and one error message. Now it has been separated. We have a message for ORA-30926 clearly explaining the reason for updating the same row twice (even providing the ROWID) as having duplicates in the source. And we’ve got a new error in 23ai – ORA-14359 (Figure 3).
Figure 3: ORA-14359 in Oracle 23ai
The description sounds like the one we had for ORA-30926 in the past, doesn’t it? But it better explains that not just some hypothetical “large DML activity” is relevant, but specifically concurrent DML modifying the WHERE clause columns. This could lead to a DML restart and if it is unsuccessful, we’ll get ORA-14359.
Changed Behavior
The new, more comprehensible and helpful error messages are one (good) thing, but to what extent has behavior also changed? Can we still see more work done than needed, when we get ORA-30926? Let’s check.
For my test I will use a slightly modified fact table SALES from Oracle’s sample schema Sales History (SH). There is no reason for my modification here, I was just using it for another test and then just reused it here. Here is the setup (Listing 1):
CREATE SEQUENCE IF NOT EXISTS SEQ_SALES;
CREATE TABLE IF NOT EXISTS SALES_TGT (
SALE_ID NUMBER PRIMARY KEY,
PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL );
INSERT INTO SALES_TGT
SELECT seq_sales.nextval as sale_id, prod_id, cust_id, time_id, amount_sold
FROM SH.SALES;
-- 5 percent we want to use for update
CREATE TABLE IF NOT EXISTS sales_src AS
SELECT sale_id, prod_id, cust_id, time_id
, ROUND(amount_sold*1.02,2) AS amount_sold
FROM sales_tgt SAMPLE (5);
-- add five duplicates
INSERT INTO sales_src (sale_id, prod_id, cust_id, time_id, amount_sold)
SELECT sale_id, prod_id, cust_id, time_id, round(amount_sold*1.01,2) amount_sold
FROM sales_src
FETCH FIRST 5 ROWS ONLY;
COMMIT;
Listing 1: Setup
As a first step, I will run a MERGE in 19c (19.26). We can see that each step in the execution plan has been started three times (column Starts). You can read more about that, why and how it happens in my old blog post.
>> MERGE /*+ gather_plan_statistics */ INTO sales_tgt s
USING sales_src a
ON ( a.sale_id = s.sale_id )
WHEN MATCHED THEN UPDATE SET s.amount_sold = a.amount_sold
WHEN NOT MATCHED THEN INSERT (sale_id, prod_id, cust_id,
time_id, amount_sold)
VALUES (seq_sales.nextval, a.prod_id, a.cust_id,
a.time_id, a.amount_sold)
Error at line 1
ORA-30926: unable to get a stable set of rows in the source tables
----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 3 | | 0 |
| 1 | MERGE | SALES_TGT | 3 | | 0 |
| 2 | VIEW | | 3 | | 50131 |
| 3 | SEQUENCE | SEQ_SALES | 3 | | 50131 |
|* 4 | HASH JOIN OUTER | | 3 | 50127 | 50131 |
| 5 | TABLE ACCESS FULL| SALES_SRC | 3 | 50127 | 150K|
| 6 | TABLE ACCESS FULL| SALES_TGT | 3 | 918K| 918K|
----------------------------------------------------------------------
Listing 2: DML restart in 19c
But running the same MERGE in Oracle 23ai (23.7 Free), we not only get a better error message but can also see that the statement stops after processing 26 rows (column A-Rows) from the target table and there are no attempts to restart (Starts=1)
>> MERGE /*+ gather_plan_statistics */ INTO sales_tgt s
USING sales_src a
ON ( a.sale_id = s.sale_id )
WHEN MATCHED THEN UPDATE SET s.amount_sold = a.amount_sold
WHEN NOT MATCHED THEN INSERT (sale_id, prod_id, cust_id,
time_id, amount_sold)
VALUES (seq_sales.nextval, a.prod_id, a.cust_id,
a.time_id, a.amount_sold)
Error at line 3
ORA-30926: The operation attempted to update the same row (rowid: 'AAARkpAAYAAAIXjAAZ') twice.
----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | | 0 |
| 1 | MERGE | SALES_TGT | 1 | | 0 |
| 2 | VIEW | | 1 | | 2 |
| 3 | SEQUENCE | SEQ_SALES | 1 | | 2 |
|* 4 | HASH JOIN OUTER | | 1 | 50221 | 2 |
| 5 | TABLE ACCESS FULL| SALES_SRC | 1 | 50221 | 50221 |
| 6 | TABLE ACCESS FULL| SALES_TGT | 1 | 918K| 26 |
----------------------------------------------------------------------
Listing 3: No DML restart in Oracle 23ai
However, it is a little confusing what I can see doing this test in my Always Free Autonomous Data Warehouse instance running Oracle 23.8 (also checked with 23.7). As you can see in Listing 4, ORA-14359 is thrown instead of ORA-30926 and we can see a restart again. First, I see this as evidence that these two errors belong together and originate in the new version from a common predecessor. Second, using this new error ORA-14359 in such a situation cannot be intentional from my point of view. This message does not describe well what the reason was and, in fact, it is the old behavior leading to a restart. I hope this will be fixed soon.
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and
Engineered Systems
Version 23.8.0.25.05
1 row selected.
MERGE /*+ gather_plan_statistics */ INTO sales_tgt s
USING sales_src a
ON ( a.sale_id = s.sale_id
WHEN MATCHED THEN UPDATE SET s.amount_sold = a.amount_sold
WHEN NOT MATCHED THEN INSERT (sale_id, prod_id, cust_id,
time_id, amount_sold)
VALUES (seq_sales.nextval, a.prod_id, a.cust_id,
a.time_id, a.amount_sold)
Error at line 7
ORA-14359: The operation was unable to obtain a consistent set of rows that needed to be modified.
----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 3 | | 0 |
| 1 | MERGE | SALES_TGT | 3 | | 0 |
| 2 | VIEW | | 3 | | 50131 |
| 3 | SEQUENCE | SEQ_SALES | 3 | | 50131 |
|* 4 | HASH JOIN OUTER | | 3 | 50127 | 50131 |
| 5 | TABLE ACCESS FULL| SALES_SRC | 3 | 50127 | 150K|
| 6 | TABLE ACCESS FULL| SALES_TGT | 3 | 918K| 918K|
----------------------------------------------------------------------
Listing 4: ORA-14359 in Always Free Autonomous Data Warehouse
In the next post I will check whether there are changes in how MERGE is handling a write consistency and statement restarts.