Following a comment from Marcin Przepiorowski on my last post, it crossed my mind to check whether “with” functions can be deterministic – the answer seems to be “not yet”. Here’s a simple script that you can run from end to end to check current and future releases – it compares inline (with) and standalone functions when the “deterministic” keyword has been used.
create table t1 as select 1 n1 from all_objects where rownum <= 10 ; execute dbms_stats.gather_table_stats(user,'t1') create or replace function standalone_function(i_in number) return number deterministic is begin dbms_output.put_line('Standalone function'); return i_in; end; / spool 12c_deterministic prompt ============ prompt arraysize 20 prompt ============ set arraysize 20 with function inline_function(i_in number) return number deterministic as begin dbms_output.put_line('Inline function'); return(i_in); end; select standalone_function(n1), inline_function(n1) from t1 /
The code is very simple – create a table with 10 rows of one column, every row holding the same value; create a standalone function declared as deterministic that simple outputs a message and returns the input value. If determinism is actually in play then, within a single database call, Oracle will call the function just once per input value rather than once per reference. The query then defines an inline (with) function, and selects using the standalone and inline functions from the table. Here’s the output:
============ arraysize 20 ============ STANDALONE_FUNCTION(N1) INLINE_FUNCTION(N1) ----------------------- ------------------- 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 10 rows selected. Standalone function Inline function Standalone function Inline function Inline function Inline function Inline function Inline function Inline function Inline function Inline function Inline function
As you can see, the inline function has been called 10 times while the standalone function has been called twice. (The two calls to the standalone function is a feature of the way that SQL*Plus fetches the first row from a query and then starts using the arraysize for subsequent fetches – each fetch call is a new database call which requires a new call to the deterministic function … try running the code with the arraysize set to 2 (or 1 – which SQL*Plus will silently change to 2).
The intent of inline (with) functions is to make PL/SQL function calls within SQL more efficient – but before you change your code to take advantage of the feature, make sure that you are going to lose the benefit of determinism.