Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Somewhat theoretical question need help on design

NikJunejaMar 27 2024

Hi All,

Hope you all are doing good.

Oracle Version:-

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.22.0.0.0

We have a process where we receive full feed from an upstream every weekend.

Record count received:- roughly 50 million records +

We assumed to get it on day 0 followed by incremental load. But upstream doesn't have the infrastructure to send delta so they send full file every weekend.

Our process was designed to merge full file with records in the final table using Oracle merge statement:-

  1. Mark all contacts as Inactive in core table
  2. Load Data in Staging Table
  3. Run Oracle Merge Statement from Staging to Core, marking those contacts as Active which are found.
  4. This merge takes all resources and runs for around 9+ hours every weekend making our system unusable.

This basic design was chosen thinking we would be required to run it for day 0 only, but now it runs every weekend.

We could have used file processing but we do not have any Solaris or Linux machines for our application.

Can anyone advise what other options we can choose to reduce the merge process using Oracle ?

Comments
Post Details
Added on Mar 27 2024
6 comments
143 views