Is there a PL/SQL pragma similar to DETERMINISTIC, but for the scope of one single SQL SELECT?

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 o开发者_StackOverflow中文版nce per t.x value (without major performance impact)?

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.

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).

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.

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>