Deterministic function call from Cursor Oracle not working

Question

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)

[The documentation](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/DETERMINISTIC-clause.html#GUID-6AECC957-27CC-4334-9F43-0FBE88F92654) says: *If a function with a DETERMINISTIC clause violates any of these semantic rules, the results of its invocation, its value, and the effect on its invoker are all **undefined**.* And: *When the DETERMINISTIC option appears, the compiler **may** use the mark to improve the performance of the execution of the function.* — astentx, Apr 14 '21 at 08:04
This one: *A DETERMINISTIC function may not have side effects.* — astentx, Apr 14 '21 at 08:34
What is interesting: if I execute the same `select` statement without cursor, it is invoked only once per value. If I add `result_cache` to the definition, it is executed once in the cursor also. Looks like so much *may* in the documentation is the reason for this: sometimes optimizer decides to reuse the calculation, sometimes not depending on some internal algorinthm. — astentx, Apr 14 '21 at 09:08

score 2 · Answer 1 · answered Apr 15 '21 at 01:35

2

In short, Deterministic functions caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc(scalar subquery caching) has no this limitation.

Please read my series of articles about deterministic functions:

answered Apr 15 '21 at 01:35

Sayan Malakshinov

8,492
1
19
27

Got it , but even after setting the array size the result is not as expected in the cursor . Can you please guide how can I make that work? – Stay Curious Apr 15 '21 at 04:02
What I mean is ...cursor query is only 1 call ,and ideally it should cache everything – Stay Curious Apr 15 '21 at 04:09
@StayCurious you are fetching one row at a time in the loop. `FETCH get_date INTO rec;` fetches just 1 row. So you are doing 9 fetch calls to get all rows (8 - to get all rows, and 1 extra fetch for no_data_found). Just check the value in v$sql.fetches for your statement – Sayan Malakshinov Apr 15 '21 at 12:57

score 1 · Accepted Answer · answered Apr 15 '21 at 10:33

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)

Deterministic function call from Cursor Oracle not working

2 Answers2