Hi Connor, Chris et al,
Could you please kindly help me better understand when ORA-30926 is to be raised for non-deterministic set of rows on input?
Up to 12.1 it worked as a charm , regardless of sort order and number duplicate rows on input ORA-30926 got raised (tested on 10.2.0.5, 12.0, 12.1).
Starting from 12.2 up to 19.4 behaviour has changed so that exception is not raised on every such occasion and 2 rows get merged instead.
Thank you in advance.
BR,
Martin Z.
drop table target purge;
drop table source purge;
create table target ( a number, b number );
create table source ( a number, b number );
insert into target values ( 1, 1 );
insert into source values ( 1, 1 );
insert into source values ( 1, 2 );
insert into source values ( 1, 3 );
--fails on ORA-30926: unable to get a stable set of rows in the source tables
merge into target
using (select * from source order by b desc) source
on (target.a = source.a)
when matched then
update set target.b = source.b;
--fails on ORA-30926: unable to get a stable set of rows in the source tables
merge into target
using (select * from source order by b asc) source
on (target.a = source.a)
when matched then
update set target.b = source.b;
--fails on ORA-30926: unable to get a stable set of rows in the source tables
merge into target
using source
on (target.a = source.a)
when matched then
update set target.b = source.b;
delete source where b = 3;
--fails on ORA-30926: unable to get a stable set of rows in the source tables
merge into target
using (select * from source order by b desc) source
on (target.a = source.a)
when matched then
update set target.b = source.b;
--2 rows merged
merge into target
using (select * from source order by b asc) source
on (target.a = source.a)
when matched then
update set target.b = source.b;
--fails on ORA-30926: unable to get a stable set of rows in the source tables
merge into target
using source
on (target.a = source.a)
when matched then
update set target.b = source.b;
rollback;
--2 rows merged
merge into target
using source
on (target.a = source.a)
when matched then
update set target.b = source.b;
This is the way merge has always worked.
I see exactly the same behaviour on 11.2.0.4 and 19.3. All statements raise ORA-30926 except the one with select * from source order by b asc.
Merge raises ORA-30926 when it tries to update the same row twice.
If to start you have
1,1
In the destination. And
1,1
1,2
source and merge processes the rows in source in that order. So first it sets
b = 1
Which is the value it already has. So this "does nothing". Then the next row updates it to 2. So the row in the target only
changes once.
But if you process the rows in the source in this order:
1,2
1,1
First you update the target b = 2. Then apply the second row to update it back to 1. This means the row has now
changed twice. Hence ORA-30926.
Note this means if all rows in the source have the same value for B, you won't see the error:
create table target ( a number, b number );
create table source ( a number, b number );
insert into target values ( 1, 1 );
insert into source values ( 1, 1 );
insert into source values ( 1, 1 );
insert into source values ( 1, 1 );
commit;
merge into target
using (select * from source order by b desc) source
on (target.a = source.a)
when matched then
update set target.b = source.b;
3 rows merged.
Unless you add an order by to the source query, merge can receive the rows from the source in any order. So if you've started seeing this behaviour after an upgrade from 12.1 -> 19.4, it's because merge receives rows in a different order. e.g. because the physical storage of the rows changed in the upgrade process.
If this is causing a problem in your application, either you need to:
- Add a deterministic sort to the source query to avoid the issue
- Ensure there is at most one row in the source for each row in the target