Scalar Subquery

-- Scalar Subquery ---

CREATE OR REPLACE FUNCTION f(     x IN VARCHAR2) RETURN NUMBER AS BEGIN dbms_application_info.set_client_info(userenv('client_info')+1); RETURN LENGTH(x); END; /

VARIABLE cpu NUMBER

-- BEGIN :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0); END; /

SELECT owner, f(owner) FROM all_objects;

SELECT dbms_utility.get_cpu_time - :cpu AS cpu_hsecs, userenv('client_info')               AS calls FROM dual;

/*

CPU_HSECS CALLS -       20 5050

-- BEGIN :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0); END; /

SELECT owner, (select f(owner) from dual) FROM all_objects;

SELECT dbms_utility.get_cpu_time - :cpu AS cpu_hsecs, userenv('client_info')               AS calls FROM dual;

CPU_HSECS CALLS -        5 4

--       -- Application

-- Instead of

SELECT * FROM t where c = plsql_function;

-- User SELECT * FROM t WHERE c = (select plsql_function);

REFERENCES: Oracle Magazine: On Caching and Evangelizing SQL by Tom Kyte (September/October 2011)