Subquery function invoked twice if alias is used in main SQL

Question

I'm trying to understand how Oracle processes SQL's to study ways of optimizing complex SQL's. Consider the test function below:

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

And the SQL below, using the created function:

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

Why does the execution take 10 seconds, and not 5? Is there a way to force the reuse of the value calculated by FCN_SLOW so that it is not executed twice?

Answer 1

The SQL engine is opting to not materialize the subquery and is pushing the function calls into the outer query where it gets called multiple times for each row. You need to force the function to be evaluated in the subquery where it is called rather than allowing the SQL engine to rewrite the query.

One method is use ROWNUM to force it to materialize the inner query:

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

Another method is to use a CTE with the (undocumented) materialize hint:

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

db<>fiddle here [which takes 20 seconds to run... not 30 seconds.]

You can see a similar example with materializing sequence values (rather than sleeping) in this answer.

Answer 2

Because you haven't indicated that the function won't produce different result the next time. The compiler/optimizer can't and shouldn't assume that.

This tells the compiler that it produces the same result from the same input (this case nothing)

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

so it just runs in 5 seconds.

Regards

** Edit I want to make clear that if you do not know how the value is acquired in the function, then you should not force it to just run once.

• It could be getting the number from a message queue
• Could have some random element in it
• Could be reading the number from a high DML table
• etc...

Take this for an example:

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

What do you think the correct behavior is? Run a1 twice or just once?

My example outputs different numbers, which is the correct behavior since each call will produce a different return:

1088614
1088645

And if you do know that the function produces the same value with the same input, then you should make it deterministic to let the compiler know it can reuse previous values.

Example:

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

Outputs just two lines, which is correct behaviour:

3,1415926535897932384626433832795028842
6,2831853071795864769252867665590057684