ORA-30926: 合并表时无法在源表中获得一组稳定的行

2021-12-27 00:00:00 merge sql oracle

我有这个合并声明:

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);

这是返回给我这个错误:

That is returning me this error:

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 提前一天时，我收到此错误.

When the target table is empty, it works. If I run it when the P.DT_LOAD is the same as B.DT_LOAD, it works. When I run it next day, when the P.DT_LOAD is one day ahead, I get this error.

有人可以帮助我吗?

提前致谢！

推荐答案

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

It's a bit tricky case. The primary reason is that you seems have duplicates in TMP_DP_REGIAO.DS_PROTHEUS_CODE column and MERGE tries to update the same row of destination table several times. But if new values and old values in updated columns are the same, Oracle can skip this issue of duplicates:

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 会引发您得到的异常:

But if old and new values are different Oracle raises the exception you get:

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

相关文章