12c Determinism

Following a comment from Marcin Przepiorowski on my last post, it crossed my mind to check whether “with” (the 12c inline) 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.

rem
rem     Script:         12c_deterministic.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2013
rem

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 not going to lose the benefit of determinism.

Update May 2019

I was prompted to review some optimisation of function calls recently, and this made me re-run this test. Things change in 12.2 – the inline function now behaves as a deterministic function.

Addendum (May 2019): A follow-up tweet from Iudith Mentzel warns that whatever has been done is at best incomplete, after testing a similar example using varchar2() on LiveSQL (19.2).  To be investigated further.