Hi Tom,
Hope you are doing well!
I have 2 cursor loops. cursor C1,cursor c2(parameterised on C1 results).
below is a pseudo code depicting the situation:
declare
cursor C1
select distinct a,b,c
from table1;--fetches 18K+records
cursor c2(p_a,p_b,p_c)
select *
from table1
where a=p_a
and b=p_b
and c=p_c;----fetches 20K+records
begin
loop c1
minor select queries and few validations....
loop c2(a,b,c)
actual validations
if insert flag='Y'
Insert into table1;
ELSE
UPDATE table1
end loop C2;
End loop C1;
END;
I am unable to use bulk collect and FORALL as I have to insert or update conditionally.
The performance impact is too high.
Question:
How can I achieve above with a better performance. Kindly help
Best Regards,
Srabanee
It may be fiddly, but you almost certainly can use BULK COLLECT & FORALL.
Define two new arrays - one for inserts, one for updates. After bulk collecting, loop through the fetched records. Load each one into either the insert or update array as appropriate. e.g.
select ... bulk collect into all_data
from ...
for i in 1 .. all_data.count loop
if <insert conditions> then
ins_data(ins_data.count+1) := all_data(i);
else
upd_data(upd_data.count+1) := all_data(i);
endif;
end loop;
forall i in 1 .. ins_data.count
insert into ...
forall i in 1 .. upd_data.count
update ...Alternatively a single MERGE may be appropriate here.