MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Stable set of rows?

with 8 comments

My students are into the upload lab and they’ve encountered the wonderful ORA-30926 error from a MERGE statement. An example of the MERGE for an upload is in this earlier blog post.

This is the wonderful error message, which doesn’t seem to have meaning for many. 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. The ON clause is where the MERGE statment matches the source query’s rows against the target table’s rows. The best join condition in a MERGE statement is one between a surrogate primary and foreign key column.

Error: ORA-30926
Text:  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.

The problem is very much like when you write what you think is a single-row subquery but find out it’s actually a multiple-row subquery when it return an ORA-01422 error in Oracle.

As a rule, I’ve found that using the DISTINCT operator in the source SELECT statement fixes it most of the time because the join isn’t returning a unique set of rows. Although, the better solution requires that you identify how to gain a unique result set. Alternatively, you need to re-examine the logic of your WHERE clause. It also happens when the SELECT clause returns date-time data types like SYSDATE instead of date data types. A date-time can be converted by using the TRUNCate function like this:

MERGE INTO TABLE_NAME target
USING
 (SELECT   ...
  ,        TRUNC(SYSDATE) AS creation_date
  FROM     ...) SOURCE
ON (target.primary_key_column = SOURCE.primary_key_column)
WHEN MATCHED THEN
UPDATE SET last_updated_by = SOURCE.last_updated_by
,          last_update_date = SOURCE.last_update_date
WHEN NOT MATCHED THEN
INSERT VALUES
( column_list_of_values );

Hope this helps my students and others … ;-)

Written by maclochlainn

March 5th, 2010 at 1:43 am

Posted in Oracle,Oracle XE,sql

8 Responses to 'Stable set of rows?'

Subscribe to comments with RSS or TrackBack to 'Stable set of rows?'.

  1. Well explained Michael, thanks.
    The other hits I got when searching the error were not only in illegible English, they missed the real cause of the error…the lack of uniqueness that you pointed out.
    Definitely worthwhile bookmarking your blog.

    Dirk

    13 Jan 11 at 9:19 am

  2. Very good post; simple and to the point. Solved my issue today. Thanks.

    Iftequar Mohammed

    10 May 12 at 7:05 am

  3. Super Weblog – Your Input has helped me!

    Kloes

    24 May 12 at 7:04 am

  4. Great input, the only one that help me after hour of tests. Thank’s

    Paula

    7 Jun 12 at 6:38 am

  5. Another hint: The ON Clause indicates a JOIN. Make sure all the JOIN fields are included and unique.

    Stephan

    27 Jun 12 at 6:28 am

  6. Use DISTINCT for a quick and dirty fix — but then re-examine the query logic for a truly correct fix. Thanks for your post!

    Danilo Piazzalunga

    28 Aug 12 at 10:05 am

  7. Distinct is not a recommended fix. It might result into un-intended results or an example of flawed business logic. I would suggest

    1) Take the select query that forms the part of the source table and run it against the database and examine the output
    2) Examine the join conditions against the target table and see what join is missing that results in a non-deterministic update.
    3) Modify the source query to get a stable set of rows

    This initial exercise might save a lot of effort on bug fixing later

    Chirayu

    29 Apr 13 at 3:46 am

  8. I second the motion made by Chirayu for 2 reasons.

    1) SELECT DISTINCT = SELECT SLOWLY.
    2) The JOIN, not the source, is usually the cause.

    The bottom line is that the source has multiple rows that match a single row in the destination, kind of like an incomplete join.

    For example . . .

    * The primary/unique key on the destination table has 2 columns (ID, and EFFECTIVE_DATE).
    * Your ON clause only joins by the ID column
    * Your source contains multiple rows with the same value for ID and different values for EFFECTIVE_DATE

    In this case, SELECT DISTINCT will not remove the “duplicates”.

    You will need to either remove the duplicates in the source by some other means [e.g. WHERE EFFECTIVE_DATE = (SELECT MAX(EFFECTIVE_DATE) FROM ...)], or improve the ON clause to use the entire PK/UK.

    Steven

    18 Jul 13 at 5:55 pm

Leave a Reply