Hi

I am migrating from Sybase IQ to Oracle 19C. there are many updates happening from one or multiple tables.

My Target_TBL Table has 18 Million records per partition and there are 1000's of Partitions. (Partitioned by VersionID). APP_ID is one of the another key column in this table.
I have 10 Partitioned tables which are partitioned by APP_ID which has around 10 Million to 15 Million Records.
I have 5 non-partitioned Lookup tables which are smaller in size.

I have rewritten all the Update statements to Merge in Oracle 19C, all the updates happen for one VersionID only which is in the where clause, and I join the source table using APP_ID and other keycolumn to update 70 to 100% of the records in each updates

1. Target table has a different key column to update the table from partitioned Source tables which are 10 to 15 Million. i have to do this by 10 different Merge Statements
2. Target Tables have different key columns to update from Non-partitioned Lookup table , I have to do this 5 different merge statements

In sybase IQ all the multiple updates are completed in 10 Minutes, in Oracle 19C it takes more than 5 hours. I have enabled parallel Query and Parallel DML also.

A) Can you suggest a better way to handle these kind of updates
B) In few places the explain plan shows (PDML disabled because single fragment or non partitioned table used) .
C) I leave the large Source table updates to go with has join's
D) I Force the Lookup source table updates to use Neste Loop. Is this good or Not ?
E) if i need to use indexes, can i go with local/global Other key column reference for Lookup tables.

Appreciate any other suggestions to handle these scenarios.


example

 Merge INTO Target_TBL 
       USING SOURCE_A
       ON (SOURCE_A.APP_ID=Target_TBL.APP_ID
           and SOURCE_A.JOB_ID=Target_TBL.JOB_ID)
When Matched then update 
set Target_TBL.email=SOURCE_A.email
Where Target_TBL.VersionID = 100
and SOURCE_A.APP_ID = 9876;

 Merge INTO Target_TBL 
       USING SECOND_B
       ON (SECOND_B.APP_ID=Target_TBL.APP_ID
           and SECOND_B.DEPT_ID=Target_TBL.DEPT_ID)
When Matched then update 
set Target_TBL.salary=SECOND_B.salary
Where Target_TBL.VersionID = 100
and SECOND_B.APP_ID = 9876;

Merge INTO Target_TBL 
       USING Lookup_C
       ON (Lookup_C.Country_ID=Lookup_C.Country_ID)
When Matched then update 
set Target_TBL.Amount_LOCAL=Lookup_C.Amount_LOCAL
Where Target_TBL.VersionID = 100;

Merge /*+ GATHER_TABLE_STATISTICS */ INTO Target_TBL 
       USING SOURCE_A
       ON (SOURCE_A.APP_ID=Target_TBL.APP_ID
           and SOURCE_A.JOB_ID=Target_TBL.JOB_ID)
When Matched then update 
set Target_TBL.email=SOURCE_A.email
Where Target_TBL.VersionID = 100
and SOURCE_A.APP_ID = 9876;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST'))

Merge /*+ GATHER_TABLE_STATISTICS */ INTO ( select * from  Target_TBL where VersionID = 100 )
       USING ( select * from  SOURCE_A where APP_ID = 9876)
       ON (SOURCE_A.APP_ID=Target_TBL.APP_ID
           and SOURCE_A.JOB_ID=Target_TBL.JOB_ID)
When Matched then update 
set Target_TBL.email=SOURCE_A.email