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 ;