Optimizing PL/SQL Code - Oracle Database 11gR2 Performance Tuning - page 257

3. Create a function called MY_VALUE , which returns an element of an array:

CREATE OR REPLACE FUNCTION MY_VALUE(ATABLE IN OUT TAB_NUMBERS,
AIND IN NUMBER) RETURN NUMBER
IS
L_VALUE NUMBER := 0;
BEGIN
L_VALUE := ATABLE(AIND);
RETURN L_VALUE;
END;
/

4. Create the function MY_VALUE_NOCOPY , which acts as the previous one, but

the array parameter is defined as NOCOPY :
CREATE OR REPLACE FUNCTION MY_VALUE_NOCOPY(
ATABLE IN OUT NOCOPY TAB_NUMBERS,
AIND IN NUMBER) RETURN NUMBER
IS
L_VALUE NUMBER := 0;
BEGIN
L_VALUE := ATABLE(AIND);
RETURN L_VALUE;
END;
/

5. Compare the performance of the solutions:

SET SERVEROUTPUT ON
DECLARE
MY_IDS TAB_NUMBERS := TAB_NUMBERS(NULL);
L_VALUE NUMBER := 0;
t1 NUMBER;
t2 NUMBER;
PROCEDURE get_time (t OUT NUMBER) IS
BEGIN
t := DBMS_UTILITY.get_time;
END;