Is there a PL/SQL pragma similar to DETERMINISTIC,

Question

In a SQL SELECT statement, I'd like to execute a function that is deterministic for the scope of that SELECT statement (or transaction would be ok, too):

select t.x, t.y, my_function(t.x) from t

Many values of t.x are the same so Oracle could omit calling the same function again and again, to speed things up. But if I label the function as DETERMINISTIC, the results may be cached between several executions of this query. The reason why I can't use DETERMINISTIC is because my_function uses a configuration parameter that is changed from time to time.

Is there any other keyword I could use? Are there any catches that I should be aware of (memory issues, concurrency, etc)? Or maybe any other tricks, such as analytic functions to call the function only once per t.x value (without major performance impact)?

Answer 1

A possible simplistic workaround would be to create a second, DETERMINISTIC function that calls the first one; but have the second function take an additional, meaningless parameter, for which you provide a different literal value in each query that uses the function.

Answer 2

Another method is to put the function in a package and set the result as a global variable. Then when you call the function check whether the input variables are the same as before and quickly return the global variable if so:

SQL> create or replace package temp is
  2
  3    function blah ( PIndex integer ) return integer;
  4
  5  end temp;
  6  /

Package created.

SQL>
SQL> create or replace package body temp is
  2
  3    GResult integer := 0;
  4    GIndex integer;
  5
  6    function blah ( PIndex integer ) return integer is
  7
  8      begin
  9
 10        if Gindex = Pindex then
 11          return Gresult;
 12        else
 13          GIndex := Pindex;
 14          GResult := Pindex;
 15        end if;
 16
 17        return Gresult;
 18
 19      end blah;
 20
 21  end temp;
 22  /

Package body created.

SQL>
SQL> select temp.blah(1) from dual;

TEMP.BLAH(1)
------------
           1

SQL>
SQL> select temp.blah(1) from dual;

TEMP.BLAH(1)
------------
           1

SQL>
SQL> select temp.blah(2) from dual;

TEMP.BLAH(2)
------------
           2

SQL>

Answer 3

This is not an answer to your question, but can be a solution for you. This configuration parameter that you've mentioned, can't be added as a parameter to function? In this case, my_function(t.x, val1) is a different thing vs my_function(t.x, val2).

Answer 4

If you do this:

select t.x, t.y, (select my_function(t.x) from dual)
from t

then Oracle can use subquery caching to achieve reduced function calls.