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

Question

I am executing a merge query to update 2 columns in a table, but I get the following error "ORA-30926: unable to get a stable set of rows in the source tables.

When I execute the merge query but I have already used a partition by and where rn=1 in the using clause to pick up only the non-duplicate records from source, but Oracle still throws the error.

What can I do to resolve this?

MERGE
/*+ parallel(A) enable_parallel_dml*/
INTO
(
  SELECT
    PAY_RANGE_START_DATE_KEY,
    AA_PERSON_NATURAL_KEY,
    AA_PERSON_ASSIGNMENT_KEY,
    SCHEDULE_LINE_ID,
    SRC_CREATED_DATE,
    SRC_LAST_UPDATE_DATE
  FROM
    EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP
)
A USING
(
SELECT
  PAY_RANGE_START_DATE_KEY,
  AA_PERSON_NATURAL_KEY,
  AA_PERSON_ASSIGNMENT_KEY,
  SCHEDULE_LINE_ID,
  SRC_CREATED_DATE,
  SRC_LAST_UPDATE_DATE
FROM
  (
    SELECT
      PAY_RANGE_START_DATE_KEY,
      AA_PERSON_NATURAL_KEY,
      AA_PERSON_ASSIGNMENT_KEY,
      SCHEDULE_LINE_ID,
      SRC_CREATED_DATE,
      SRC_LAST_UPDATE_DATE,
      ROW_NUMBER() OVER ( PARTITION BY PAY_RANGE_START_DATE_KEY,
      AA_PERSON_NATURAL_KEY, AA_PERSON_ASSIGNMENT_KEY, SCHEDULE_LINE_ID,
      SRC_CREATED_DATE, SRC_LAST_UPDATE_DATE ORDER BY ROWNUM ) AS rn
    FROM
      EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP_FRS_356
  )
WHERE
  rn = 1
)
B ON
(
  A.PAY_RANGE_START_DATE_KEY = B.PAY_RANGE_START_DATE_KEY AND
  A.AA_PERSON_NATURAL_KEY    = B.AA_PERSON_NATURAL_KEY AND
  A.AA_PERSON_ASSIGNMENT_KEY = B.AA_PERSON_ASSIGNMENT_KEY AND
  A.SCHEDULE_LINE_ID         = B.SCHEDULE_LINE_ID
)
WHEN MATCHED THEN
  UPDATE
  SET
    A.SRC_CREATED_DATE     = B.SRC_CREATED_DATE,
    A.SRC_LAST_UPDATE_DATE = B.SRC_LAST_UPDATE_DATE
  WHERE
    A.SRC_CREATED_DATE      <> B.SRC_CREATED_DATE
  OR A.SRC_LAST_UPDATE_DATE <> B.SRC_LAST_UPDATE_DATE;
   COMMIT;

score 2 · Accepted Answer · edited Jun 19 '21 at 09:36

2

I have already used a partition by and where rn=1 in the using clause to pick up only the non-duplicate records from source, but Oracle still throws the error.

Your

ROW_NUMBER() OVER ( PARTITION BY PAY_RANGE_START_DATE_KEY,
      AA_PERSON_NATURAL_KEY, AA_PERSON_ASSIGNMENT_KEY, SCHEDULE_LINE_ID,
      SRC_CREATED_DATE, SRC_LAST_UPDATE_DATE ORDER BY ROWNUM ) AS rn

with a filter rn=1 removes duplicates by 6 columns, while you are using 4 columns in ON() clause. Moreover you are using another filter to filter rows for update.

The easiest way to get what you want is to filter needed data in USING clause:

MERGE
/*+ parallel(A) enable_parallel_dml*/
INTO
(
  SELECT
    PAY_RANGE_START_DATE_KEY,
    AA_PERSON_NATURAL_KEY,
    AA_PERSON_ASSIGNMENT_KEY,
    SCHEDULE_LINE_ID,
    SRC_CREATED_DATE,
    SRC_LAST_UPDATE_DATE
  FROM
    EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP
)
A USING
(
SELECT
  PAY_RANGE_START_DATE_KEY,
  AA_PERSON_NATURAL_KEY,
  AA_PERSON_ASSIGNMENT_KEY,
  SCHEDULE_LINE_ID,
  SRC_CREATED_DATE,
  SRC_LAST_UPDATE_DATE
FROM
  (
    SELECT
      BB.PAY_RANGE_START_DATE_KEY,
      BB.AA_PERSON_NATURAL_KEY,
      BB.AA_PERSON_ASSIGNMENT_KEY,
      BB.SCHEDULE_LINE_ID,
      BB.SRC_CREATED_DATE,
      BB.SRC_LAST_UPDATE_DATE,
      ROW_NUMBER() OVER ( 
         PARTITION BY BB.PAY_RANGE_START_DATE_KEY,
                      BB.AA_PERSON_NATURAL_KEY, 
                      BB.AA_PERSON_ASSIGNMENT_KEY, 
                      BB.SCHEDULE_LINE_ID
         ORDER BY ROWNUM /*?*/ 
         ) AS rn
    FROM
      EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP_FRS_356 BB
     ,EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP AA
    WHERE
      AA.PAY_RANGE_START_DATE_KEY = BB.PAY_RANGE_START_DATE_KEY AND
      AA.AA_PERSON_NATURAL_KEY    = BB.AA_PERSON_NATURAL_KEY AND
      AA.AA_PERSON_ASSIGNMENT_KEY = BB.AA_PERSON_ASSIGNMENT_KEY AND
      AA.SCHEDULE_LINE_ID         = BB.SCHEDULE_LINE_ID
      AND (
          AA.SRC_CREATED_DATE     <> BB.SRC_CREATED_DATE
       OR AA.SRC_LAST_UPDATE_DATE <> BB.SRC_LAST_UPDATE_DATE
      )
  )
WHERE
  rn = 1
)
B ON
(
  A.PAY_RANGE_START_DATE_KEY = B.PAY_RANGE_START_DATE_KEY AND
  A.AA_PERSON_NATURAL_KEY    = B.AA_PERSON_NATURAL_KEY AND
  A.AA_PERSON_ASSIGNMENT_KEY = B.AA_PERSON_ASSIGNMENT_KEY AND
  A.SCHEDULE_LINE_ID         = B.SCHEDULE_LINE_ID
)
WHEN MATCHED THEN
  UPDATE
  SET
    A.SRC_CREATED_DATE     = B.SRC_CREATED_DATE,
    A.SRC_LAST_UPDATE_DATE = B.SRC_LAST_UPDATE_DATE;

As you can see I filtered rows you don't need in the USING clause, so you don't need WHERE clause in UPDATE SET and removed duplicates by 4 columns used for matching

edited Jun 19 '21 at 09:36

halfer

19,824
17
99
186

answered May 15 '21 at 07:47

Sayan Malakshinov

8,492
1
19
27

the query which you suggested is running for a very long time is there a way to tune it as were are updating 118M records using the above logic – karthik May 15 '21 at 12:42
You need to provide more info: execution plan, sql monitor, tables ddl and statistics, etc – Sayan Malakshinov May 15 '21 at 13:00
I have added the execution plan for the using clause alone , as running it alone is taking more than 2 hours by now and it has not completed as of yet – karthik May 15 '21 at 13:20
Ok, first of all, replace `order by rownum` with just `order by null`, it will remove extra operation `couny`. Second you need to gather table statistics for both tables, since your explain plan shows 1 row. – Sayan Malakshinov May 15 '21 at 13:28
I have replaced the order with null and gathered stats on both tables. Please find the latest explain plan attached. – karthik May 15 '21 at 13:50
Ok, I see... 22 mln rows and 1.2bln rows... Try to add parallel hint for this `select` and check how long does it work. Then add `aa.rowid as rid ` into inner view in using clause and `rid` into top level main select of using clause and replace on() with `on(a.rowid=b.rid)` and check resulted execution plan of it – Sayan Malakshinov May 15 '21 at 14:53
@ Sayan Malakshinov I have added the explain plan after adding parallel hint's however I am unable to add the row id as per what you have mentioned, it is throwing the following error "cannot select ROWID from, or sample, a join view without a key-preserved table" – karthik May 15 '21 at 16:50
As I said previously `aa.rowid as rid` must be in the inner view, not top level select. On top level select must be `rid` – Sayan Malakshinov May 15 '21 at 16:50
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/232457/discussion-between-karthik-and-sayan-malakshinov). – karthik May 15 '21 at 16:51
is there any other alternative way to simplify the query? like using max () – karthik May 15 '21 at 17:46
I said to replace whole `on(...) ` clause with `on(a.rowid=b.rid)` you don't need previous 4 conditions since they were filtered already in using clause – Sayan Malakshinov May 15 '21 at 18:12
And to be honest, that's another question and you need to create another topic for the query optimization – Sayan Malakshinov May 15 '21 at 18:14

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

1 Answers1