If I'm using oracle sql and plsql to do computations on an employee, and then selecting more data based on the result of those computations... will it be faster to select the all the data I may need all at once when selecting the employee (assume something like 20 rows with 5 columns each) and then use that data as a local array, or select just the one row I will need when finished?
-- Example with multiple selects
declare
l_employeeID number;
l_birthday date;
l_horoscope varchar2;
begin
select employeeID into l_employeeID from employeeTbl t where t.rownum = 1;
l_birthday := get_birthdayFromID(l_employeeID);
select horoscope into l_horoscope from horoscopeTable t where l_birthday between l_horoscope.fromDate and l_horoscope.toDate;
return l_horoscope;
end;
-- Example with table selection, and loop iteration
declare
l_empolyeeID number;
l_birthday date;
l_horoscope varchar2;
l_horoscopeDates date_table;
begin
select employeeID, cast(multiset(select horoscopeDate from horoscopeTable)) as date_table into l_employeeID, l_horoscopeDates from employeeTbl t where t.rownum = 1;
l_birthday := get_birthdayFromID(l_employeeID);
for i in 1 .. l_horoscopeDates.count - 1 loop
if l_birthday between l_horoscopeDates(i) and l_horoscopeDates(i + 1) then
return l_horoscopeDates(i);
end if;
end loop;
return null;
end;
I understand that I'm paying more ram and IO to select additional data, but is it more efficient than incurring another context switch to call the sql when the extra data is not significantly larger than needed?
