PLSQL Programs Make Function Calls as Efficient as Possible BEGIN -- Inefficient, calls my_function for every row. FOR item IN (SELECT DISTINCT(SQRT(department_id)) col_alias FROM employees) LOOP dbms_output.put_line(item.col_alias); END LOOP; -- Efficient, only calls function once for each distinct value. FOR item IN ( SELECT SQRT(department_id) col_alias FROM ( SELECT DISTINCT department_id FROM employees) ) LOOP dbms_output.put_line(item.col_alias); END LOOP; END; / --Pipeline Function CREATE TYPE type_arr AS OBJECT (id NUMBER, name VARCHAR2(30)); CREATE TYPE infycle_typ AS TABLE OF type_arr; / CREATE OR REPLACE FUNCTION collection_fn(p_no number) RETURN infycle_typ IS v_data infycle_typ := infycle_typ ( ); BEGIN FOR i IN 1..p_no LOOP v_data.EXTEND; v_data(v_data.LAST) := type_arr( id => i , name => 'Name_'|| i); END LOOP; RETURN v_data; END; / CREATE OR REPLACE FUNCTION collection_fn(p_no number) RETURN infycle_typ PIPELINED IS v_data infycle_typ := infycle_typ ( ); BEGIN FOR i IN 1..p_no LOOP PIPE ROW(type_arr( id => i , name => 'Name_'|| i)); END LOOP; RETURN; END; / FETCH -> BULK COLLECT WE can Set LIMIT Option For UPDATE, INSERT and DELETE we can Prefer FORALL statement We can use Bind Variable To improve the Performance