是否存在与DETERMINISTIC类似的PL/SQL编译指示，但仅适用于单个SQL SELECT的范围? [英] Is there a PL/SQL pragma similar to DETERMINISTIC, but for the scope of one single SQL SELECT?

问题描述

在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

t.x的许多值都相同，因此Oracle可以省略一次又一次地调用同一函数来加快处理速度.但是，如果我将该函数标记为DETERMINISTIC，则结果可能会在此查询的多次执行之间进行缓存.我不能使用DETERMINISTIC的原因是my_function使用的配置参数会不时更改.

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.

还有其他我可以使用的关键字吗?我应该注意哪些陷阱(内存问题，并发性等)?还是其他技巧，例如分析函数，每个t.x值仅调用一次函数(不会对性能造成重大影响)?

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

推荐答案

如果您这样做:

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

然后，Oracle可以使用子查询缓存实现精简函数调用.

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

这篇关于是否存在与DETERMINISTIC类似的PL/SQL编译指示，但仅适用于单个SQL SELECT的范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！