ORA-30926:合并表时，无法在源表中获取稳定的行集

Question

我有一条Merge语句：

MERGE INTO TB_DP_REGIAO B
USING TMP_DP_REGIAO P
ON (P.DS_PROTHEUS_CODE = B.DS_PROTHEUS_CODE)
WHEN MATCHED THEN UPDATE SET B.DS_PLANNING_CODE = CASE WHEN B.DT_LOAD < P.DT_LOAD THEN P.DS_PLANNING_CODE ELSE B.DS_PLANNING_CODE END,
                             B.DT_LOAD = CASE WHEN B.DT_LOAD < P.DT_LOAD THEN P.DT_LOAD ELSE B.DT_LOAD END
WHEN NOT MATCHED THEN INSERT(B.DS_PROTHEUS_CODE, B.DS_PLANNING_CODE, B.DT_LOAD) VALUES(P.DS_PROTHEUS_CODE, P.DS_PLANNING_CODE, P.DT_LOAD);

返回给我这个错误：

Error starting at line 1 in command:
MERGE INTO TB_DP_REGIAO B
USING TMP_DP_REGIAO P
ON (P.DS_PROTHEUS_CODE = B.DS_PROTHEUS_CODE)
WHEN MATCHED THEN UPDATE SET B.DS_PLANNING_CODE = CASE WHEN B.DT_LOAD < P.DT_LOAD THEN P.DS_PLANNING_CODE ELSE B.DS_PLANNING_CODE END,
                             B.DT_LOAD = CASE WHEN B.DT_LOAD < P.DT_LOAD THEN P.DT_LOAD ELSE B.DT_LOAD END
WHEN NOT MATCHED THEN INSERT(B.DS_PROTHEUS_CODE, B.DS_PLANNING_CODE, B.DT_LOAD) VALUES(P.DS_PROTHEUS_CODE, P.DS_PLANNING_CODE, P.DT_LOAD)
Error report:
SQL Error: ORA-30926: unable to get a stable set of rows in the source tables
30926. 00000 -  "unable to get a stable set of rows in the source tables"
*Cause:    A stable set of rows could not be got because of large dml
           activity or a non-deterministic where clause.
*Action:   Remove any non-deterministic where clauses and reissue the dml.

当目标表为空时，它将正常工作。如果我在P.DT_LOAD与B.DT_LOAD相同的情况下运行它，它就可以工作。当我第二天运行它时，当P.DT_LOAD提前一天时，我得到这个错误。

有人能帮我这个忙吗？

提前感谢！

Answer 1

这是一个有点棘手的案例。主要原因是您的TMP_DP_REGIAO.DS_PROTHEUS_CODE列中似乎有重复项，而MERGE多次尝试更新目标表的同一行。但是，如果更新列中的新值和旧值相同，Oracle可以跳过此重复问题：

SQL> select * from t;

      CODE TEXT                                                                 
---------- ----------                                                           
         1 test                                                                 

SQL> merge into t using (
  2   select 1 code,'test' text from dual union all
  3   select 1 code,'test' text from dual
  4  ) s
  5  on (t.code = s.code)
  6  when matched then
  7    update set t.text = s.text
  8  /

2 rows merged 

但是如果旧值和新值不同，Oracle会抛出异常：

SQL> merge into t using (
  2   select 1 code,'a' text from dual union all
  3   select 1 code,'a' text from dual
  4  ) s
  5  on (t.code = s.code)
  6  when matched then
  7    update set t.text = s.text
  8  /
merge into t using (
           *
error in line 1:
ORA-30926: unable to get a stable set of rows in the source tables 

Answer 2

此问题的另一个原因也可能是ON子句中指定的条件。当目标行与源行之间有一对多的映射时，就会出现这个错误，这可能是由于两个原因。

1) there are duplicate rows in source table.
2) there are unique rows in source table, but ON clause conditions are pointing to multiple rows in the source table.

在第二种情况下，必须修改ON子句条件，以分别在目标表和源表中实现一对一或多对一的映射。