Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

FORALL vs MERGE

SuriMar 10 2021 — edited Mar 10 2021

Hi,
We have two large tables T1, T2( more than 200 million records ) in our database. We have a requirement where we need to update few more tables by joining T1 and T2. I thought of developing a procedure using below approaches.
Can you please suggest which is the best practice. ( I am leaning towards approach 1 )
Note: Our application is OLTP and multiple users will be executing the procedure same time.

approach1:

procedure bulk_update is
begin
   MERGE INTO t3
    using ( select col1, col2 from t1 join t2 on <> )
   ....

   MERGE INTO t4
    using ( select col1, col2 from t1 join t2 on <> )
......
MERGE INTO t5
    using ( select col1, col2 from t1 join t2 on <> )
......   
end bulk_update;

approach2:

procedure bulk_update is

  cursor get_bulk_data IS select col1, col2 from t1 join t2 on (... );
begin
   
   OPEN get_bulk_data;
    loop
     FETCH get_bulk_data  INTO collection LIMITS 1000;
     
     FORALL .. update t3;     
     
     FORALL .. update t4;

     FORALL .. update t5;     
     
     FORALL .. update t6;
     
     exit when collection.count=0;
    end loop;
   CLOSE get_bulk_data;

  
end bulk_update;
Comments
Post Details
Added on Mar 10 2021
4 comments
4,518 views