Saturday, December 17, 2005

ORA-30926 and MERGE

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

When i got a phone call reporting this error message, the problem sounded much more threatening than it actually turned out to be later on.

After doing some homework, that is some google and metalink search, I asked for a copy of the source code raising this error and it turned out to be a simple problem: the join conditions specified in the ON clause of the MERGE statement were not on a unique key.

The MERGE statement works on the assumption that a 1:1 or a 1:0 relationship exists between the source table (or view) and the target table. This means that when you write the ON clause, the columns you specify there should be either the primary key or a unique key for both the source and the target table.

For instance, if you have two tables having both a primary key made up of 3 columns, you can't specify just two of them in the ON clause, otherwise you'll get this error at runtime, provided the source values are not unique by pure chance.

The somewhat erratic behavior of the error is really the only bad aspect of this problem.

CREATE TABLE target
(a NUMBER, b NUMBER, c NUMBER, d NUMBER,
constraint pk_target primary key(a,b,c) using index)

/
CREATE TABLE source (a NUMBER, b NUMBER, c NUMBER, d NUMBER,
constraint pk_source primary key(a,b,c) using index)
/
INSERT INTO target
values (1,1,1,1);
/
INSERT INTO target
values (1,1,2,2);
/
INSERT INTO target
values (1,1,3,3);
/
INSERT INTO source
values (1,1,1,1);
/
INSERT INTO source
values (1,1,2,2);
/
INSERT INTO source
values (1,1,3,3);
/

MERGE INTO target t1
USING (SELECT a, b, c, d from source) t2
ON (t1.a = t2.a
AND t1.b = t2.b)

WHEN MATCHED THEN
UPDATE SET t1.c = t2.c,
t1.d = t2.d
WHEN NOT MATCHED THEN
INSERT (a, b, c, d)
VALUES (t2.a, t2.b, t2.c, t2.d)
/

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

ROLLBACK
/

But now let's try with a different set of values that will make our join to work anyway and see that we won't get any error this time:

INSERT INTO target
values (1,1,1,1);
/
INSERT INTO target
values (2,2,2,2);
/
INSERT INTO target
values (3,3,3,3);
/
INSERT INTO source
values (1,1,1,1);
/
INSERT INTO source
values (2,2,2,2);
/
INSERT INTO source
values (3,3,3,3);
/


MERGE INTO target t1
USING (SELECT a, b, c, d from source) t2
ON (t1.a = t2.a
AND t1.b = t2.b)

WHEN MATCHED THEN
UPDATE SET t1.c = t2.c,
t1.d = 2 * t2.d
WHEN NOT MATCHED THEN
INSERT (a, b, c, d)
VALUES (t2.a, t2.b, t2.c, t2.d)
/






ABCD
1112
2224
3336



See message translations for ORA-30926 and search additional resources



ORA-30926: impossibile ottenere un set di righe stabile nelle tabelle origine
ORA-30926: no se ha podido obtener un juego de filas estable en las tablas de origen
ORA-30926: no es pot obtenir un conjunt de files estable a les taules d'origen
ORA-30926: impossible d'obtenir un ensemble de lignes stables dans les tables source
ORA-30926: Stabile Zeilengruppe in den Quelltabellen kann nicht eingelesen werden
ORA-30926: η λήψη ενός σταθερού συνόλου γραμμών από τους πίνακες προέλευσης δεν είναι δυνατή
ORA-30926: kan ikke hente et stabilt sæt rækker i kildetabellerne
ORA-30926: kan inte hämta en stabil uppsättning rader i källtabellerna
ORA-30926: kan ikke hente et stabilt sett av rader i kildetabellene
ORA-30926: lähdetauluista ei saada vakaata rivijoukkoa
ORA-30926: nem olvasható be állandó sorkészlet a forrástáblákból
ORA-30926: nu s-a putut obţine un set de rânduri stabil în tabelele sursă
ORA-30926: Kan geen stabiele rijenset ophalen uit de brontabellen.
ORA-30926: não foi possível obter um conjunto de linhas estável nas tabelas de origem
ORA-30926: incapaz de obter um conjunto de linhas estável nas tabelas de origem
ORA-30926: невозможно получить устойчивый набор строк в исходных таблицах
ORA-30926: nelze získat stabilní sadu řádků ve zdrojových tabulkách
ORA-30926: nemožno získať stabilnú množinu riadkov v zdrojových tabuľkách
ORA-30926: nie można uzyskać stabilnego zestawu wierszy z tabel źródłowych
ORA-30926: kaynak tablolarında sabit bir satır kümesi alınamıyor

16 comments:

Anonymous said...

ah, that helped me, bad oracle error messages :(

Anonymous said...

Until now i only knew this error if you try to update a column which you also use in the join.

Merge into table1 t
using (...) src
on (t.the_column = src.the_column)
when matched then
update set t.the_column = src.the_column;

Cold said...

Very nice article! I had been searching for a while trying to find why my simple query causes this error. Now I know that what I want to do is not possible short of PL/SQL. Thanks!

Europa said...

How about this one for odd:
I have a select statement in the using clause that returns no rows yet for some reason the merge inserts 3 rows into the table. Where are the rows coming from if not from the using clause???

The table I'm merging into is a global temporary table without a primary key. I will try adding a primary key to the table I'm merging into and see if that fixes it. Thanks.

Byte64 said...

this sounds really odd, you should try to build a simple test case and toss it in the OTN SQL forum...

Bye,
Flavio

Anonymous said...

Thanks for this. Very clear, unlike the 30926 Error Message and unlike the Metalink explanation.

Ps. Mr 18th Feb 2008, according to Metalink you get an ORA-904 if you try to update a column used in the ON clause.
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=137202.1

Anonymous said...

I'm using as source table a virtual table like (select * from table( ...). All entries are unique. I'm joining using unique key from target. With small number of rows in the virtual table it works but having more than 300 it fails raising this error.

Byte64 said...

interesting, what version of db are you running on?

can you (re)create a simple test case?

However, assuming that what you say is 100% correct, this problem sounds similar to that bug i came across in another posting.

Cheers,
Flavio

Mau said...

Thank you, your explanation was very helpful actually. I was going crazy with this issue.
Now, I can have dinner with my wife :=)

Byte64 said...

Mau,
there is nothing so frustrating like an elusive SQL error while the dinner is already served!

:-)

Flavio

Anonymous said...

Ooooohhhhhhh ..... I see now. Okay, yeah. I can FIX that!!!

Martin said...

But this seem to also work:
DROP TABLE target PURGE;
DROP TABLE source PURGE;

Table dropped.

bee_code@BEEDB>
Table dropped.

CREATE TABLE target(a NUMBER, b NUMBER, c NUMBER, d NUMBER,constraint pk_target primary key(a,b,c) using index);

Table created.

CREATE TABLE source (a NUMBER, b NUMBER, c NUMBER, d NUMBER,constraint pk_source primary key(a,b,c) using index);
INSERT INTO target values (1,1,1,1);
INSERT INTO target values (1,1,2,2);
INSERT INTO target values (1,1,3,3);
Table created.

bee_code@BEEDB>
1 row created.

bee_code@BEEDB>
1 row created.

bee_code@BEEDB> INSERT INTO target values (1,1,3,3);

1 row created.

INSERT INTO source values (1,1,1,1);

1 row created.

INSERT INTO source values (1,1,2,2);

1 row created.

INSERT INTO source values (1,1,3,3);

1 row created.

bee_code@BEEDB> MERGE INTO target t1 USING (SELECT a, b, c, d from source) t2 ON (t1.a = t2.a AND t1.b = t2.b) WHEN MATCHED THEN UPDATE SET t1.c = t2.c, t1.d = t2.d WHERE (t1.c=t2.c) WHEN NOT MATCHED THEN INSERT (a, b, c, d) VALUES (t2.a, t2.b, t2.c, t2.d);

3 rows merged.

Byte64 said...

Martin,
that makes sense because the c column, as far as this join (t1.c = t2.c) is concerned, contains unique values in both tables, which makes the statement succeed.
As long as there are no duplicate values in the evaluation of the join, MERGE will work, if any duplicates are detected, it will fail.

Flavio

Byte64 said...

In other words, by adding that additional WHERE clause, it's like adding a further AND inside the ON clause.
Or at least this is my interpretation...

Anonymous said...

Thankss, I could not figure out how to put an primary key is the ON statement. Reading this I noticed for a compound key you can add the key members together in side the on stament

FriendlyCharm said...

this has help me a lot . thanks for everything i did some research but your explanation turn out to be great!!!

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles