Scalar subquery caching

Fri, 2006.05.12 - 11:49 — müzso

This is a feature introduced in Oracle 9i. The point is that the RDBMS can cache results of subqueries and provide a significant performance gain in certain cases.

Lets assume that you use some complex logic in a function and want to query its results in a query. Your table has some thousand rows, but the column that you want to run the function for has a lot less distinct values. In this case your function should be executed only for the distinct values for the column, but previously the DB executed it for each row.

You can use scalar subquery caching to avoid unnecessary executions.

Eg.

SET SERVEROUTPUT ON



CREATE PACKAGE pkg_ssc_test IS
cnt NUMBER;
END pkg_ssc_test;
/



CREATE FUNCTION ssc_test(p_input IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  pkg_ssc_test.cnt := pkg_ssc_test.cnt + 1;
  RETURN SUBSTR(p_input, 1, 4);
END ssc_test;
/



exec pkg_ssc_test.cnt := 0;



SELECT
  object_type,
  ssc_test(object_type) AS ssc_output
FROM
  all_objects ao
WHERE 1 = 1
  AND ROWNUM <= 10
/



exec dbms_output.put_line('count: ' || pkg_ssc_test.cnt);
exec pkg_ssc_test.cnt := 0;



SELECT
  object_type,
  (SELECT ssc_test(object_type) FROM dual) AS ssc_output
FROM
  all_objects ao
WHERE 1 = 1
  AND ROWNUM <= 10
/



exec dbms_output.put_line('count: ' || pkg_ssc_test.cnt);