Insert/update loop ORACLE function

bulk-insertinsertmergeoracle

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
)

loop

merge into people_destionation dest
  using ( select firstname, lastname, email, id from dual ) 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 
(
rec.firstname,
rec.lastname,
rec.email,
rec.id
);

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.

Best Answer

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, ...).

Related Question