Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

merge

sql

oracle

I have this Merge statement:

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.

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.

Can someone help me on this?

Thanks in advance!

like image 511
Lucas Rezende Avatar asked Feb 21 '14 13:02

Lucas Rezende


People also ask

How to fix ORA-30926 unable to get a stable set of rows in the source tables?

A further clarification to the use of DISTINCT to resolve error ORA-30926 in the general case: You need to ensure that the set of data specified by the USING() clause has no duplicate values of the join columns, i.e. the columns in the ON() clause.

What is ORA-30926?

"ORA-30926: unable to get a stable set of rows in the source tables" means some row in the destination table (stg in this case) matches more than one row in the source table (h).

What is non deterministic where clause?

The key is the non-deterministic where clauses phrase. That phrase means that the the query inside the USING clause returns a non-unique set of rows. The returned rows can't be matched uniquely against the target table in the ON clause.


2 Answers

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 

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 
like image 113
Dmitry Nikiforov Avatar answered Sep 20 '22 06:09

Dmitry Nikiforov


Another reason for this problem could also be the conditions specified in ON clause. This error occurs when there is 1 to many mapping to your destination rows vs source rows respectively which can be due to two reasons.

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.

In second case the ON clause conditions has to modified to achieve 1 to 1 or many to one mapping in destination and source table respectively.

like image 29
Pawan Rawat Avatar answered Sep 21 '22 06:09

Pawan Rawat