Scalar Subquery Caching Behavior in a SQL Statement

What is scalar subquery caching?

Scalar subquery caching is an Oracle SQL query optimization technique where the database caches the result of a scalar subquery (a subquery that returns exactly one value ) when it is uncorrelated, meaning it does not depend on any row data from the outer query.

This is a simple but effective optimization technique that reduces the number of recursive function calls. You can take advantage of it by wrapping your PL/SQL function calls in a SELECT ... FROM DUAL, allowing the optimizer to cache the result and reuse it whenever possible. It’s somewhat a little bit similar to using a deterministic function. For a comparison of these two mechanisms, be sure to check out the mind-blowing blog post by Sayan Malakshinov: Deterministic function vs scalar subquery caching. Part 1.

A Simple Demonstration

I will create a table, a sequence for a counter, and an inline function (counter_function) – Prepare Phase:

SQL> CREATE TABLE my_table (col1 VARCHAR2(20),col2 VARCHAR2(20));
SQL> INSERT INTO my_table VALUES ('Insanedba','is cool');
SQL> INSERT INTO my_table VALUES ('Insanedba','is awesome');
SQL> COMMIT;

SQL> CREATE SEQUENCE call_counter_seq ;

Now i will query the scalar subquery statement for all the table records and see how much counter_function is executed.

-- Demonstrate scalar subquery calls
SQL> WITH
FUNCTION counter_function(p_val VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    -- Increment the sequence to track calls
    DECLARE n NUMBER;
    BEGIN
      SELECT call_counter_seq.NEXTVAL INTO n FROM dual;
    END;
    RETURN 'INSANE DBA';
  END;
SELECT (select counter_function(col1) from dual) AS result
FROM my_table;
/


RESULT
--------------------------------------------------------------------------------
INSANE DBA
INSANE DBA

SQL> SELECT call_counter_seq.CURRVAL AS function_call_count FROM dual;

FUNCTION_CALL_COUNT
-------------------
                  1

However, you should be cautious and mindful of the potential outcomes when wrapping PL/SQL function calls in this way.

For example, you have a randomUUID function that produces type 4 UUIDs (Universally Unique Identifiers in the 8-4-4-4-12 hexadecimal format, totaling 36 characters with hyphens), similar to Java’s UUID.randomUUID(). You expect it to return a unique identifier with each call, as shown in the example below.

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "UUID_GENERATOR" AS
import java.util.UUID;

public class UUID_GENERATOR {
  public static String randomUUID() {
    return UUID.randomUUID().toString();
  }
}
/

SQL> CREATE OR REPLACE FUNCTION randomUUID
RETURN VARCHAR2 AS
  LANGUAGE JAVA
  NAME 'UUID_GENERATOR.randomUUID() return java.lang.String';
/


SQL> select randomUUID() as uuid from dba_objects where rownum<3;

UUID
_______________________________________
6a13244e-8fc1-4452-af70-b1a7105fbd3c
aebf3d10-8b77-480d-b802-0264a52cf665

Everything behaves normally until you wrap the function call in a SELECT from DUAL, where Oracle may apply scalar subquery caching, causing the same UUID to be returned unexpectedly.

SQL>  select (select randomUUID() as uuid from dual) as uuid from dba_objects where rownum<3;

UUID
_______________________________________
1a34b8db-36b4-4496-b92f-0a1f5c4b2906
1a34b8db-36b4-4496-b92f-0a1f5c4b2906

Oracle caches the result of scalar subqueries for performance reasons. Since the subquery doesn’t depend on anything from the outer query (no correlation), Oracle runs it once and reuses the result for every row in the outer query. This is an optimization technique, not a bug.

Conclusion:

Scalar subquery caching is a valuable optimization technique in Oracle SQL that enhances performance by reusing the results of uncorrelated scalar subqueries. By caching the results, Oracle avoids redundant evaluations, reducing recursive function calls and improving query execution speed. However, if you need your function to be executed for each row without being cached (such as in the case of UUID generation), you should avoid using scalar subquery caching in your queries. Understanding and utilizing scalar subquery caching appropriately can lead to significant performance benefits in SQL queries.

Hope it helps. See you on the next post : Part 3 : Why NO_DATA_FOUND Behavior Differs in SQL and PL/SQL