pl / sql函数调用了多少次？

Assume you have the following update:

假设您有以下更新:

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

The func function is evaluated two times for every row, or once for every row?

func函数每行评估两次,或每行评估一次?

Thanks,

3 个解决方案

#1

This is the kind of situation where some experimentation is useful (this was conducted on 10g). Using the following query, we can tell that normal functions, using the same parameters (in this case, none) will be executed each time they are called:

这种情况下某些实验很有用(这是在10g上进行的)。使用以下查询,我们可以告诉每次调用它们时,将使用相同的参数(在本例中为none)执行正常的函数:

select dbms_random.value() from all_tables

This is because Oracle assumes that a function will not return the same value consistently unless you tell it otherwise. We can do that by creating a function using the deterministic keyword:

这是因为Oracle假定函数不会一致地返回相同的值,除非您另有说明。我们可以通过使用deterministic关键字创建函数来实现:

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

Using this function instead of dbms_random in the first query tells us that the query is being executed only once, despite the many calls. But this only clarifies the select section. What if we use the same deterministic function in both a select and a where clause. We can test that using the following query:

在第一个查询中使用此函数而不是dbms_random告诉我们,尽管有很多调用,但查询只执行一次。但这只是澄清了选择部分。如果我们在select和where子句中使用相同的确定性函数,该怎么办?我们可以使用以下查询来测试:

SELECT rand_det
FROM   all_tables
WHERE  rand_det > .5;

You may have to run this several times to see our proof, but, eventually, you'll see a list of values less than 0.5. This provides us with evidence that even the deterministic function is being executed twice: once for each section it appears in. As an alternative, you can modify our deterministic function as follows, then run the subsequent query, which will reveal 2 lines written to DBMS_OUTPUT.

您可能需要多次运行才能看到我们的证明,但最终,您会看到一个小于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;

#2

While I like Allan's answer for showing how to investigate this, I think the real lesson to take away is that you shouldn't rely on the answer to this question if you can avoid it.

虽然我喜欢Allan的回答来展示如何调查这一点,但我认为真正的教训是,如果你可以避免,你不应该依赖这个问题的答案。

Here's a useful post on the topic from Tom Kyte ("I've written thousands of times that you cannot rely on how many times or when or if SQL will call your function.") Even prior to 11g introducing the result cache, there are no guarantees about how many times a function will be called in processing a SQL statement. It can depend on the execution plan, which can change over time.

这是Tom Kyte关于这个主题的有用帖子(“我已经写了数千次,你不能依赖多少次,或者SQL何时或者是否会调用你的函数。”)甚至在11g引入结果缓存之前,有无法保证在处理SQL语句时调用函数的次数。它可能取决于执行计划,执行计划可能会随时间而变化。

If your concern is performance and your function is deterministic, the 11g result cache is probably sufficient -- it won't guarantee a specific limit on the number of calls to the function, but should reduce the number of redundant calls significantly. (See link provided in @cularis answer.)

如果您关注的是性能并且您的功能是确定性的,则11g结果缓存可能已足够 - 它不能保证对函数调用次数的特定限制,但应显着减少冗余调用的数量。 (参见@cularis回答中提供的链接。)

If for some reason you actually want to ensure that the two calls to the functions are distinct, I think the only way you could force that would be to add a second parameter to the function that is actually ignored but serves to prevent the result cache or optimizer from seeing the calls as identical.

如果由于某种原因你确实想要确保对函数的两次调用是不同的,我认为你可以强制执行的唯一方法是向实际被忽略的函数添加第二个参数但是用于防止结果缓存或优化器看到调用是相同的。

#3

For 11g, it will be called once for every new occurence of col2. For the next calls, a cached result is used if caching is enabled.

对于11g,每次出现col2时都会调用一次。对于下一次调用,如果启用了缓存,则使用缓存结果。

#1