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.