I wanted to know if we have any technique by which we can calculate the value which needed to be set for a LIMIT clause of bulk collect operation. For example below, lets say our cursor has 10 Million records..What is the value which we can set for LIMIT clause to have optimum performance. Is there any way we can calculate it.
decalre
cursor c_emp is <some select query>
var <variable> ;
begin
open c_emp;
loop
fetch c_emp bulk collect into var limit 2;
exit when c_emp%NOTFOUND;
end loop;
close c_emp;
end;
See Question&Answers more detail:os