Ittichai: Created page with "

  -- Scalar Subquery ---   CREATE OR REPLACE   FUNCTION f(       x IN VARCHAR2)     RETURN NUMBER   AS   BEGIN     dbms_application_info.set_client_info(userenv('client_info..."

2012-05-14T20:50:20Z

Created page with "<pre> -- Scalar Subquery --- CREATE OR REPLACE FUNCTION f( x IN VARCHAR2) RETURN NUMBER AS BEGIN dbms_application_info.set_client_info(userenv('client_info..."

New page

<pre>

-- 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());

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

[[Category:Database_Scripts]]

Scalar Subquery - Revision history

Ittichai: Created page with "
-- Scalar Subquery --- CREATE OR REPLACE FUNCTION f( x IN VARCHAR2) RETURN NUMBER AS BEGIN dbms_application_info.set_client_info(userenv('client_info..."