Subquery function invoked twice if alias is used in main SQL-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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