Collections Vs. Pipelined Table Function

PIPELINED Table functions enables to return sets of data when the next set of data is being processed. This is obviously better than normal TABLE functions. Because, normal TABLE functions picks and processes all the data at one shot then returns it - so its particularly useful for ETL (Extraction - Transformation - Load).

My opinion - For not too complex operations its better to use COLLECTIONS / GLOBAL TEMPORARY Tables rather than Pipelined Table functions. As the extraction / transformation and load is faster than via pipelined table functions. Following is a quick demonstration.

TABLE CREATION SCRIPTS

SQL> drop table coll_temp_source;

Table dropped.

SQL> CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level <= 25); 2

drop table coll_temp_target5;
CREATE TABLE COLL_TEMP_TARGET5 AS SELECT * FROM ALL_OBJECTS WHERE 1 = 2;


Table created.

SQL> SQL>
Table dropped.

SQL>
Table created.

SQL> SQL>
SQL> drop sequence dummy_seq1;

Sequence dropped.

SQL> create sequence dummy_seq1 start with 1
2 /

Sequence created.


PIPELINED TABLE FUNCTION CREATION SCRIPT

SET TIMING ON

CREATE OR REPLACE FUNCTION pipe_fun (p_cursor IN SYS_REFCURSOR )
RETURN t_expected PIPELINED
PARALLEL_ENABLE (PARTITION p_cursor BY ANY)
AS
TYPE g_object_list IS TABLE OF coll_temp_source%ROWTYPE INDEX BY PLS_INTEGER;
g_object_tab g_object_list;
g_error_level NUMBER;
v_object_id NUMBER;
l_row t_validate_row := t_validate_row(NULL, NULL, NULL,NULL, NULL, NULL,NULL, NULL, NULL,NULL, NULL, NULL,NULL);

BEGIN

LOOP
g_error_level := 2;
FETCH p_cursor BULK COLLECT INTO g_object_tab LIMIT 300;


FOR i IN g_object_tab.FIRST .. g_object_tab.LAST LOOP

g_error_level := 3;
l_row.OWNER := g_object_tab(i).OWNER ;
l_row.OBJECT_NAME := g_object_tab(i).OBJECT_NAME ;
l_row.SUBOBJECT_NAME := g_object_tab(i).SUBOBJECT_NAME ;
l_row.DATA_OBJECT_ID := g_object_tab(i).DATA_OBJECT_ID ;
l_row.OBJECT_TYPE := g_object_tab(i).OBJECT_TYPE ;
l_row.CREATED := g_object_tab(i).CREATED ;
l_row.LAST_DDL_TIME := g_object_tab(i).LAST_DDL_TIME ;
l_row.TIMESTAMP := g_object_tab(i).TIMESTAMP ;
l_row.STATUS := g_object_tab(i).STATUS ;
l_row.TEMPORARY := g_object_tab(i).TEMPORARY ;
l_row.GENERATED := g_object_tab(i).GENERATED ;
l_row.SECONDARY := g_object_tab(i).SECONDARY ;
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
l_row.object_id := v_object_id;

IF l_row.owner = 'SYS' THEN
l_row.secondary := 'Y';
END IF;
IF l_row.object_type = 'JAVA CLASS' THEN
l_row.temporary := 'Y';
END IF;
PIPE ROW (l_row);
END LOOP;
EXIT WHEN p_cursor%NOTFOUND;
END LOOP;
CLOSE p_cursor;
RETURN;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/


SQL> @pipe_func.sql

Function created.

Elapsed: 00:00:00.10
SQL>
SQL> INSERT INTO COLL_TEMP_TARGET5
SELECT *
FROM TABLE(
pipe_fun (
cursor(SELECT * FROM COLL_TEMP_SOURCE))) 2 3 4 5 ;

761125 rows created.

Elapsed: 00:01:38.91
SQL>


COLLECTIONS

SET TIMING ON
SET SERVEROUTPUT ON SIZE 10000

DECLARE
TYPE g_object_list IS TABLE OF coll_temp_source%ROWTYPE INDEX BY PLS_INTEGER;
g_object_tab g_object_list;
g_error_level NUMBER;
v_object_id NUMBER;

CURSOR cur_coll_temp IS
SELECT *
FROM coll_temp_source;

BEGIN
g_error_level := 1;
OPEN cur_coll_temp;

LOOP
g_error_level := 2;
FETCH cur_coll_temp BULK COLLECT INTO g_object_tab LIMIT 300;

g_error_level := 3;
EXIT WHEN cur_coll_temp%NOTFOUND;

FOR i IN g_object_tab.FIRST .. g_object_tab.LAST LOOP
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
g_object_tab(i).object_id := v_object_id;
IF g_object_tab(i).owner = 'SYS' THEN
g_object_tab(i).secondary := 'Y';
END IF;

IF g_object_tab(i).object_type = 'JAVA CLASS' THEN
g_object_tab(i).temporary := 'Y';
END IF;
END LOOP;

FORALL i IN g_object_tab.FIRST .. g_object_tab.LAST
INSERT INTO coll_temp_target5 VALUES g_object_tab(i);

COMMIT;

END LOOP;

g_error_level := 10;
CLOSE cur_coll_temp;


EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @collections.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:58.99
SQL>