ORA-30926:合并表时无法在源表中获得稳定的行集 [英] ORA-30926: unable to get a stable set of rows in the source tables when Merging tables

问题描述

我有以下合并声明:

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提前一天时，出现此错误.

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 

这篇关于ORA-30926:合并表时无法在源表中获得稳定的行集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！