Subquery function invoked twice if alias is used in main SQL

ghz 1years ago ⋅ 606 views

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

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](https://asktom.oracle.com/pls/apex/asktom.search?tag=materalized- hint)) 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.

#oracle