Impact when used in WHERE clause
The following example tries to demonstrate the impact of a deterministic function when used in a
where clause of a
select statement.
The
PL/SQL package contains two functions of which one is deterministic and the other is not. These functions produce the same result: they multiply the passed number by two.
A special feature of these functions is that they call
dbms_lock.sleep to artificially prolongate the time spent in these functions. This will allow to roughly estimate how many times the function was called.
create or replace package tq84_pkg as
function f_non_deterministic (p in number) return number;
function f_deterministic (p in number) return number deterministic;
end tq84_pkg;
/
create or replace package body tq84_pkg as
function f_non_deterministic (p in number) return number is
begin
dbms_lock.sleep(1);
return p*2;
end f_non_deterministic;
function f_deterministic (p in number) return number deterministic is
begin
dbms_lock.sleep(1);
return p*2;
end f_deterministic;
end tq84_pkg;
/
This is the table on which we're going to query a record:
create table tq84_tab (
id number,
tx varchar2(10)
);
insert into tq84_tab values (1, 'one');
insert into tq84_tab values (2, 'two');
insert into tq84_tab values (3, 'three');
commit;
Finally the select statements.
set timing on
select * from tq84_tab where id = tq84_pkg.f_non_deterministic(1);
select * from tq84_tab where id = tq84_pkg.f_deterministic(1);
The first select statement takes approximately three seconds because the table is queried in a full table scan and the non-deterministic function is called for each record during the scan.
However, the second select statement takes approximately one second because the function, by virtue of its determinacy, is called only once.
Caching of result
The result of deterministic functions for a given combination of parameter values is cached within one fetch in a select statement. In each individual fetch, the result for a parameter-value combination is cached again and thus entails another function call.
This behaviour is demonstrated with the following PL/SQL and SQL snippets:
We need a log table to record each call of the deterministic function.
create table tq84_call_log (
tm timestamp(6),
param_value number
);
The deterministic function itself.
create or replace function tq84_double(i number) return number deterministic
is
procedure write_log_entry
is
pragma autonomous_transaction;
begin
insert into tq84_call_log values (systimestamp, i);
commit;
end write_log_entry;
begin
write_log_entry;
return i*2;
end tq84_double;
/
Calling the function from an SQL statement.
set rowprefetch 20
set arraysize 99
truncate table tq84_call_log;
select
mod(level-1, 17) a,
tq84_double(mod(level-1, 17)) b
from
dual connect by level <= 1000;
After executing the previous select statement in SQL*Plus, the log table contains 187 (=11 Fetches with 17 calls to the function) records. (For a reason that I don't understand, when doing the same test in SQL Developer, I get 186 records only).
select count(*) from tq84_call_log;
Cleaning up
drop table tq84_call_log;
drop function tq84_double;