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;