PL/SQL函数被调用了多少次？

Question

假设您有以下更新内容：

Update table set col1 = func(col2)
where col1<>func(col2)

每一行的func函数会被评估两次，还是每一行只评估一次？谢谢。

- Florin Ghita

---

我需要Oracle 9i的行为... - Florin Ghita

这有帮助吗？https://dev59.com/O1DTa4cB1Zd3GeqPIE0g - Jacob

不对，那个答案是错误的... 在 select 和 where 中不是同一个函数。 - Florin Ghita

Answer 1

这种情况下，一些实验是有益的（本次实验是在10g上进行的）。使用以下查询，我们可以知道普通函数在每次调用时都会执行相同的参数（在此例中为none）：

select dbms_random.value() from all_tables

这是因为Oracle默认情况下认为一个函数不会始终返回相同的值，除非你明确告诉它。我们可以使用deterministic关键字创建一个函数以告诉Oracle该函数将始终返回相同的结果：

CREATE FUNCTION rand_det
   RETURN NUMBER
   DETERMINISTIC AS
BEGIN
   RETURN DBMS_RANDOM.VALUE ();
END;

在第一个查询中，使用这个函数代替dbms_random可以告诉我们查询仅被执行一次，尽管有多次调用。但这只是澄清了select部分。如果我们在select和where子句中都使用相同的确定性函数会发生什么呢？我们可以使用以下查询进行测试：

SELECT rand_det
FROM   all_tables
WHERE  rand_det > .5;

你可能需要多次运行此操作才能看到我们的证明，但最终你将会看到一组小于0.5的值列表。这为我们提供了证据，即即使是确定性函数也会被执行两次：每个部分都会执行一次。作为替代方案，您可以按以下方式修改我们的确定性函数，然后运行后续查询，这将显示写入DBMS_OUTPUT的2条线路。

CREATE OR REPLACE FUNCTION rand_det
   RETURN NUMBER
   DETERMINISTIC AS
BEGIN
   DBMS_OUTPUT.put_line ('Called!');
   RETURN DBMS_RANDOM.VALUE ();
END;

SELECT rand_det
FROM   all_tables;

- Allan

---

+1 个实验证据。这是哪个版本的 Oracle？ - DCookie

Answer 2

虽然我喜欢Allan的回答，因为它展示了如何调查此问题，但我认为真正需要学习的教训是如果可能的话，不应该依赖于这个问题的答案。

这里是有用的文章关于Tom Kyte的主题（“我已经写了成千上万次，你不能依赖SQL会调用函数的次数或时间。”）即使在11g之前介绍结果缓存，也不能保证一个SQL语句处理中函数被调用的次数。 它可以取决于执行计划，而执行计划随时间而改变。

如果您关心的是性能并且您的函数是确定性的，则11g结果缓存可能足够 - 它不会保证对函数调用的特定限制，但应显着减少冗余调用的数量。（请参见@cularis答案中提供的链接。）

如果出于某些原因您实际上想确保两次对函数的调用是不同的，我认为您唯一能强制执行的方法就是为函数添加第二个参数，该参数实际上被忽略但可防止结果缓存或优化器将调用视为相同。

- Dave Costa

Answer 3

对于11g版本，每当col2出现新的情况时，它将被调用一次。如果启用了缓存，则下一次调用将使用缓存的结果。

- Jacob

---

哇，这太酷了，超出了我的预期。但在9.2中？它是每行调用一次还是两次？ - Florin Ghita

这是可以证明是错误的。如果它普遍成立，那么这个查询将会为所有行返回相同的值：select dbms_random.value() from all_tables。 - Allan

我会怀疑缓存被禁用了，同时随机值是通过一个种子作为输入生成的。因此，输入每次都不相同。 - Jacob

@cularis：value 函数在使用时没有参数，因此 Oracle 不知道是否涉及种子。数据库引擎不会尝试解析函数背后的代码以确定是否涉及来自外部源的值，它只会查看规范中可用的信息。最终，这不应该归结为我们中谁认为自己更了解：如果您能证明 Oracle 的行为与您所建议的相同，那么请这样做。 - Allan

在SQL处理过程中，我不会仅依靠结果缓存作为函数对于每个输入值最多只被调用一次的“保证”。缓存未命中是有可能发生的。此外，值得强调的是（虽然在提供的链接中已经提到），这种缓存必须针对每个函数单独启用。 - Dave Costa