如果主SQL中使用别名，则调用两次子查询函数

Question

我试图了解Oracle是如何处理SQL的，以研究优化复杂SQL的方法。

CREATE OR REPLACE FUNCTION FCN_SLOW
  RETURN NUMBER IS
BEGIN
  DBMS_LOCK.SLEEP (5); --5 seconds
  RETURN 0;
END FCN_SLOW;

以及下面的SQL，使用创建的函数：

SELECT A1 + A1
  FROM (SELECT FCN_SLOW () AS A1
          FROM DUAL)

为什么执行只需要10秒，而不是5秒？是否有一种方法强制重用由FCN_SLOW计算的值，使其不被执行两次？

Answer 1

SQL引擎选择不将子查询具体化，并将函数调用推入外部查询，在该查询中，每一行被多次调用。您需要强制在调用函数的子查询中计算函数，而不是允许SQL引擎重写查询。

一种方法是使用ROWNUM强制它实现内部查询：

SELECT A1 + A1
  FROM (SELECT FCN_SLOW () AS A1
          FROM DUAL
         WHERE ROWNUM >= 1)

另一种方法是使用带有(无证) materialize提示的materialize：

WITH slow_query(a1) AS (
  SELECT /*+ materialize */
         FCN_SLOW ()
  FROM   DUAL
)
SELECT A1 + A1
  FROM slow_query

db<>fiddle https://dbfiddle.uk/?rdbms=oracle_21&fiddle=f0609eb5c11c02b57dc82735f7c19e85需要20秒才能运行.不是30秒。

您可以看到类似的示例，在这个答案中物化序列值(而不是睡眠)。

Answer 2

因为您还没有表示函数下次不会产生不同的结果。编译器/优化器不能也不应该假设这一点。

这告诉编译器，它从相同的输入产生相同的结果(本例中为空)

create or replace function fcn_slow return number deterministic is
begin
  dbms_lock.sleep(5); --5 seconds
  return 0;
end fcn_slow;

所以它只需5秒就能运行。

问候

**编辑我想说明，如果您不知道这个值是如何在函数中获得的，那么您应该而不是强制它只运行一次。

• 它可能是从消息队列中获取数字。
• 里面可能有一些随机元素
• 可能是从高DML表读取数字。
• 等等。

举个例子：

with function a1 return number is 
  l_num number;
begin
  select round(to_number(to_char(systimestamp, 'SSxFF')) * 100000, 0) into l_num from dual;
  dbms_output.put_line(l_num);
  return l_num;
end;
select a1 + a1 from dual

你认为正确的行为是什么？运行a1两次还是只运行一次？

我的示例输出不同的数字，这是正确的行为，因为每个调用都会产生不同的返回：

1088614
1088645

如果您知道函数产生的值与输入相同，那么应该让编译器知道它可以重用以前的值，这是确定性的。

示例：

with function pi(p_multi in number default 1) return number deterministic is 
  l_pi number;
begin
    select 2*asin(1) * p_multi into l_pi from dual;
    dbms_output.put_line(l_pi);
    return l_pi;
end;
select pi + pi(2) + pi(2) from dual

只输出两行，这是正确的行为：

3,1415926535897932384626433832795028842
6,2831853071795864769252867665590057684