Oracle 非确定性行集的merge语句中未引发ORA-30926

嗨，康纳，克里斯等人，

您能否帮助我更好地理解何时对输入的非确定性行集提出ORA-30926？

高达12.1，它作为一个魅力，无论排序顺序和数量重复的行在输入ORA-30926被提出 (测试10.2.0.5，12.0，12.1)。
从12.2开始到19.4行为已更改，因此不会在每种情况下引发异常，而是合并2行。

提前谢谢你。

BR，
马丁·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;

这就是merge一直以来的工作方式。

我在11.2.0.4和19.3上看到完全相同的行为。除b asc从源顺序中选择 * 的语句外，所有语句都ORA-30926。

当它尝试两次更新同一行时，合并会引发ORA-30926。

如果要开始，你有

1,1

在目的地。和

1,1
1,2

源和合并按照该顺序处理源中的行。所以首先它设置

b = 1

这是它已经拥有的价值。所以这 “什么都不做”。然后下一行将其更新为2。所以只有目标中的行 changes 一次。

但是，如果您按照以下顺序处理源中的行:

1,2
1,1

首先你更新目标b = 2。然后应用第二行将其更新回1。这意味着该行现在已经 changed twice 。因此ORA-30926。

请注意，这意味着如果源中的所有行都具有相同的B值，则不会看到错误:

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)