I want to find the best solution for an insert/update oracle function. My task is to collect some date from multiple tables (source tables) and bring them to a single table (destination table). Source tables getting inserts and updates in real time. My function must run ones/day to getting all new data and updated data to destination table. I identify my new / updated data by using a column (date) from source table based on a trigger witch update the date column with systimestamp at every insert/update. My current code looks like this

for rec in (
select firstname, lastname, email, id
      from people_source 
where people_source.date > last_run_date


merge into people_destionation dest
  using ( select firstname, lastname, email, id from dual ) src
  on (src.id=dest.id)
 when matched then updated set
when not matched then

exception when others ....

This solution make what I want, but I tested for 100k rows. I have source tables with 100 mil. rows and I try to find the best performance solution. I also read about "Switching between PL/SQL and SQL engines" and I think this applies to me. Will it be a better idea to try to make functions with BULK COLLECT and FORALL?

I have not a very long experience in pl/sql, so I need more opinions and I thing here is a good place. Thanks. Best wishes.

  • 1
    BUG DETECTED - Unless you have a RAISE/RAISE_APPLICATION_ERROR in your WHEN OTHERS clause, you have a bug. PS - why are you looping? You can do that in a single MERGE statement Nov 24, 2020 at 10:17
  • Yes I have, all errors goes into a error table. Hmm, I did the loop because I want to take only the new/updated rows from source. Does it work without a loop?
    – Luc
    Nov 24, 2020 at 10:58
  • 2
    Instead of trying to create your own procedures/tables, this sounds like a textbook case for a Materialized View.
    – EdStevens
    Nov 24, 2020 at 13:30
  • I thought about this, but I need tables for this, because this data are going in other databases and so on.
    – Luc
    Nov 24, 2020 at 13:41

1 Answer 1


In your code, you are issueing one SQL statement for every row you get from your people_source table. This is called row-by-row (read slow-by-slow) processing. For every query you make you force a context switch from PL/SQL to SQL engine and back which will add up pretty quickly.

As a first step, you should try to implement the functionality using just SQL (in this way, you eliminate expensive SQL/PLSQL context switch altogether). Try to use the merge DML as it was meant to be used:

merge into people_destionation dest
using (
  select firstname, lastname, email, id
    from people_source 
   where people_source.date > last_run_date
) src on (src.id = dest.id)
when matched then
  updated set firstname = src.firstname,
              lastname = src.lastname,
              email = src.email
when not matched then
  insert (firstname, lastname, email, id)
  values (src.firstname, src.lastname, src.email, src.id)

In case this is slow, then we need more information (for example how many rows you expect to be merged, how are source/target tables created, are there any triggers, indexes) to be able to propose a way to make it faster (adding index on Id column, parallel execution, partitioning the table, eliminating triggers, disabling/recreating indexes, ...).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.