How can we execute SQL Merge query in chunks?

Question

I have a merge query which works fine for few records ( < 500 )

Merge into TableA A using TableB B on (UNIQUEID = UNIQUEID) 
  when matched 
    then update 
      set
        A.id = B.id,
  when not matched 
    then insert (
      A.id
    )
    values(
      B.id
    )

The issue occurs for large data sets ( > 7000 )

Error : MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source

I want the merge statement to be executed in chunks ( say 1000 at once ), so that the exact issue with data can be identified.

@a_horse_with_no_name I know its a data issue that's why i want to execute it in chunks . It is not practical to into in ~10k records manually every time a data issue occurs — Dheeraj vats, Dec 05 '18 at 15:09
what do you try to achieve? Do you want to process as much records as possible before the exception occurs? In this case I propose to use BULK COLLECT with LIMIT to fetch all IDs which you want to process and later FOR ALL. Another solution is [DML Error Logging](https://oracle-base.com/articles/10g/dml-error-logging-10gr2) — RGruca, Dec 05 '18 at 15:21
*"I want the merge statement to be executed in chunks ( say 1000 at once ), so that the exact issue with data can be identified."* - That's not necessarily how you identify data issues. Basically you need to find duplicate records as the `UNIQUEID` in one table is not `UNIQUE` in another one. Use a query like [this](https://stackoverflow.com/a/59242/7998591) to identify which records are duplicating and decide what has to be done with them before proceeding. You may then alter your `MERGE` query accordingly. — Kaushik Nayak, Dec 05 '18 at 15:32
I already tried that, @KaushikNayak The UNIQUEID filed is primary key in TableB, so there is no possibility of duplication in that and while inserting data in TableA since the error occurs halfway through the execution, faulty record is not being inserted and hence i am unable to identify it — Dheeraj vats, Dec 05 '18 at 15:39
Show us the exact query you tried to identify the duplicates , you should basically check duplicates in `TableA` matching the unique ids in `TableB`. — Kaushik Nayak, Dec 05 '18 at 15:45

score 0 · Answer 1 · answered Dec 05 '18 at 15:45

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

This error indicates the database is unable to match one record in tableA with one record in tableB. This makes the MERGE outcome unpredictable, and Oracle doesn't like unpredictable outcomes.

The UNIQUEID filed is primary key in TableB, so there is no possibility of duplication in that

So you need to find the duplicate occurrences in tableA. Here is one way to do it:

select a.uniqueid
       , count(*)
from tablea a
group by a.uniqueid having count(*) > 1

Possibly you might want to join to tableB, either for performance reasons, or to validate that tableb.uniqueid actually is as unique as you think it is:

select a.uniqueid
       , count(*)
from tablea a
     join tableb b on b.uniqueid = a.uniqueid
group by a.uniqueid having count(*) > 1

How can we execute SQL Merge query in chunks?

1 Answers1