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>
精彩评论