是否有类似于 DETERMINISTIC 的 PL/SQL pragma，但仅针对单个 SQL SELECT 的范围? [英] Is there a PL/SQL pragma similar to DETERMINISTIC, but for the scope of one single SQL SELECT?

问题描述

在 SQL SELECT 语句中，我想执行一个对该 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 pragma，但仅针对单个 SQL SELECT 的范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！