Skip to Main Content
  • Questions
  • Update Partition table from another Partition table Performance issue


Question and Answer

Connor McDonald

Thanks for the question, Sree.

Asked: March 13, 2024 - 3:27 am UTC

Last updated: March 21, 2024 - 12:29 am UTC

Version: 19C

Viewed 1000+ times

You Asked


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.

 Merge INTO Target_TBL 
           and SOURCE_A.JOB_ID=Target_TBL.JOB_ID)
When Matched then update 
Where Target_TBL.VersionID = 100
and SOURCE_A.APP_ID = 9876;

 Merge INTO Target_TBL 
           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;

and Connor said...

1) Run a few sample MERGE commands with the GATHER_TABLE_STATISTICS hint so we can see what's going on, ie

           and SOURCE_A.JOB_ID=Target_TBL.JOB_ID)
When Matched then update 
Where Target_TBL.VersionID = 100
and SOURCE_A.APP_ID = 9876;

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

2) The "WHERE" is only being applied at UPDATE time, so you are doing a full scan of Target_TBL / SOURCE_A every time. Are you you didn't want something like:

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


  (3 ratings)


A reader, March 18, 2024 - 5:21 pm UTC

I did add the Where in Using, and now added the where in INTO Table also. i see difference in few places

Have another question.

I have both Source and target table with 500 + columns, when i restrict the select coulumn's to 5 ot 10 instead of having "select *". i see the bit difference in Bytes in my explain plan. Is that really helps in performance or in memory usage?

Chris Saxon
March 18, 2024 - 5:46 pm UTC

Bytes in the explain plan is the estimated about of data the operation returns. Selecting only the columns you need => the query processes less data.

This means the database needs less memory to process the query & has many other performance advantages. Tanel Poder has compiled a list at:

A reader, March 18, 2024 - 6:25 pm UTC


When i Update the Partition table by using parallel and it takes some time to run. I cannot add new partitions , the table gets locked. is there any other alternative ways
Connor McDonald
March 21, 2024 - 12:29 am UTC

See my initial answer - we need to see some data with /*+ GATHER_TABLE_STATISTICS */

A reader, March 18, 2024 - 6:44 pm UTC

Table Name : Source
Partition KEY : Version_ID
Columns, Version_id, job_code, Salary, .......
I am updating the salary from other table for the version_id and including job_code in the condition. I alway's update within the partition. is it good to have global index or local index.
Should i create index on (Version_id, Job_code) or just Job_code with local index. Please explain which is good.
i did few experiments , getting mixed response time.

Connor McDonald
March 21, 2024 - 12:29 am UTC

As per previous.

More to Explore


Need more information on hints? Check out the complete hint descriptions