Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Merge Statement is taking very long time to update records in Oracle.

Karthik.CMMay 8 2021

We are using a merge query to UPDATE 2 columns from a source table that has the same structure as the target table. But when executed the merge query keeps running for a very long time. The source table PSP_LABOR_SCHD_DAY_F_ROLLUP_FRS_356 has "22603114" rows and the target table "EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP" to which update must be done consists of "1118892748" rows. The target table has an index on these columns.
AA_PERSON_NATURAL_KEY,
AA_PERSON_ASSIGNMENT_KEY,
HR_PAY_PERIOD_KEY,
PAY_RANGE_START_DATE_KEY,
SCHEDULE_LINE_ID
Please find the explain plan for the same as well, can someone help me with this scenario

<img src="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/01L0UWFQSJER/image.png" alt="image.png">MERGE INTO EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP A USING
EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP_FRS_356 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 ;
This post has been answered by Jonathan Lewis on May 10 2021
Jump to Answer
Comments
Post Details
Added on May 8 2021
19 comments
16,562 views