sql - ORA-30926 : unable to get a stable set of rows in the source tables when Merging tables

Question

我有这个合并语句:

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