I have created a simple deterministic function , and I am calling it using a select query in a cursor as shown below
CREATE TABLE TEMP
(dt DATE);
INSERT INTO TEMP
SELECT SYSDATE FROM DUAL CONNECT BY LEVEL<=3;
INSERT INTO TEMP
SELECT SYSDATE+1 FROM DUAL CONNECT BY LEVEL<=3;
COMMIT;
--2 distinct values
SELECT DISTINCT dt from TEMP;
PACKAGE FUNCTION
CREATE OR REPLACE PACKAGE dummy_fun
AUTHID CURRENT_USER
IS
FUNCTION get_data(
p_date IN DATE)
RETURN DATE
DETERMINISTIC;
END dummy_fun;
/
CREATE OR REPLACE PACKAGE BODY dummy_fun
IS
FUNCTION get_data(
p_date IN DATE)
RETURN DATE
DETERMINISTIC
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('get_data with input (p_date=>'||p_date||' called)');
RETURN p_date+1;
END get_data;
END dummy_fun;
/
FUNCTION CALL - Expectation that get_data is been called only twice for 2 distinct dates , whereas , if I call this SQL only , it run only only two times
DECLARE
CURSOR get_date
IS
SELECT dummy_fun.get_data (
dt) from
TEMP;
rec get_date%ROWTYPE;
v_date date;
BEGIN
OPEN get_date;
LOOP
FETCH get_date INTO rec;
EXIT WHEN get_date%NOTFOUND;
NULL;
END LOOP;
CLOSE get_date;
END;
/
OUTPUT
get_data with input (p_date=>14-APR-21 called)
get_data with input (p_date=>14-APR-21 called)
get_data with input (p_date=>14-APR-21 called)
get_data with input (p_date=>14-APR-21 called)
get_data with input (p_date=>24-APR-21 called)
get_data with input (p_date=>24-APR-21 called)
get_data with input (p_date=>24-APR-21 called)
get_data with input (p_date=>24-APR-21 called)
**WITH BELOW CHANGES IT IS WORKING IN CURSOR **
CHANGE 1 - IF THE FUNCTION IS CALLED IN THE WHERE CLAUSE
CURSOR get_date
IS
SELECT 1 from
TEMP
WHERE trunc(sysdate+1)= trunc(ae9_common_code.dummy_fun.get_data (
dt))
CHANGE 2 - Kind of Scalar subquery
CURSOR get_date
IS
SELECT * FROM (
SELECT ae9_common_code.dummy_fun.get_data (
dt) from
TEMP
WHERE 1=1)
CHANGE 3 - BULK COLLECT
SELECT ae9_common_code.dummy_fun.get_data (
dt) BULK COLLECT INTO v_dates from
TEMP
WHERE 1=1;
##OUTPUT FOR ALL THE ABOVE CHANGES ARE##
get_data with input (p_date=>14-APR-21 called)
get_data with input (p_date=>24-APR-21 called)
When you have:
open cur;
loop
fetch cur into ...
end loop;
The database is fetching exactly one row at a time. As @SayanMalakshinov has noted, the database does not cache deterministic results across fetches.
What might help make this clearer is using bulk collect with a limit to get 1, 2 or more rows at a time:
create or replace procedure fetch_rows ( num_rows int ) as
cursor get_date is
select dummy_fun.get_data ( dt )
from temp;
type rec_tab is table of get_date%rowtype
index by pls_integer;
rws rec_tab;
begin
open get_date;
loop
fetch get_date
bulk collect into rws
limit num_rows;
exit when get_date%notfound;
end loop;
close get_date;
end;
/
exec fetch_rows ( 1 );
get_data with input (p_date=>14-APR-2021 10:32:36 called)
get_data with input (p_date=>14-APR-2021 10:32:36 called)
get_data with input (p_date=>14-APR-2021 10:32:36 called)
get_data with input (p_date=>15-APR-2021 10:32:36 called)
get_data with input (p_date=>15-APR-2021 10:32:36 called)
get_data with input (p_date=>15-APR-2021 10:32:36 called)
exec fetch_rows ( 2 );
get_data with input (p_date=>14-APR-2021 10:32:36 called)
get_data with input (p_date=>14-APR-2021 10:32:36 called)
get_data with input (p_date=>15-APR-2021 10:32:36 called)
get_data with input (p_date=>15-APR-2021 10:32:36 called)
exec fetch_rows ( 3 );
get_data with input (p_date=>14-APR-2021 10:32:36 called)
get_data with input (p_date=>15-APR-2021 10:32:36 called)
With a limit of 1, every row is a new fetch, so there's no caching. Set it to 2 and (potentially) every other row is cached. Up it to three and there's up to 2 rows cached per fetch, etc.
Single row fetches are slow for a whole bunch of other reasons too, so really you should be looking to use bulk collect with a limit of at least 100 anyway.
Note that the PL/SQL engine optimizes cursor-for loops to fetch 100 rows at a time, so you could also get the caching effect by writing the loop like this:
begin
for rws in (
select dummy_fun.get_data ( dt )
from temp
)
loop
null;
end loop;
end;
/
get_data with input (p_date=>14-APR-2021 10:32:36 called)
get_data with input (p_date=>15-APR-2021 10:32:36 called)