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);
You can see that the function was executed only as many times as many different values the object_type column contained in the selected rows.
Check
this article from Burleson and
this thread from Tom for further details.
Recent comments
2 years 17 weeks ago
3 years 39 weeks ago
3 years 39 weeks ago
3 years 41 weeks ago
3 years 42 weeks ago
3 years 49 weeks ago
3 years 49 weeks ago
3 years 49 weeks ago
3 years 49 weeks ago
3 years 49 weeks ago