Scalar Subquery
Jump to navigation
Jump to search
-- 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)