setting ARRAYSIZE and/or ROWPREFETCHSIZE impacts deterministic function result caching. basically, the caching kicks in only within one fetch, so the bigger the fetch size - the more beneficial it is. as of 2023-7-17 I am not entirely sure why we see efficient caching even for arraysize=1 if order by is introduced

f_deterministic_arraysize.log

SQL>
SQL> create or replace function fd(i number) return number
  2  deterministic
  3  as
  4  begin
  5      dbms_output.put_line(i);
  6      return i;
  7  end;
  8  /

Function created.

SQL>
SQL> create table t as select rownum i from dual connect by rownum <= 20;

Table created.

SQL>
SQL> set feedback only
SQL>
SQL> -- no order by - expected behavior, deterministic benefits only large arraysize
SQL> select fd(mod(i, 2)) from t
  2  .
SQL>
SQL> set rowprefetch 1
SQL> set arraysize 1
SQL>
SQL> /

20 rows selected.

1
0
1
0
1
0
1
0
1
0
1
0
1
0
1
0
1
0
1
0
SQL>
SQL> set arraysize 5000
SQL> /

20 rows selected.

1
0
1
SQL>
SQL> -- added order by - caching works for both arraysize values
SQL> select fd(mod(i, 2)) from (select * from t order by i)
  2  .
SQL>
SQL> set rowprefetch 1
SQL> set arraysize 1
SQL> /

20 rows selected.

1
0
SQL>
SQL> set arraysize 5000
SQL> /

20 rows selected.

1
0
SQL>

f_deterministic_arraysize.sql

set echo on 
set serverout on 

whenever sqlerror continue 
drop table t purge;
whenever sqlerror exit failure

cl scr 

create or replace function fd(i number) return number 
deterministic
as 
begin 
    dbms_output.put_line(i);
    return i;
end;
/

create table t as select rownum i from dual connect by rownum <= 20;

set feedback only

-- no order by - expected behavior, deterministic benefits only large arraysize
select fd(mod(i, 2)) from t
.

set rowprefetch 1
set arraysize 1
/

set arraysize 5000
/

-- added order by - caching works for both arraysize values
select fd(mod(i, 2)) from (select * from t order by i)
.

set rowprefetch 1
set arraysize 1
/

set arraysize 5000
/