TEMPORARY TABLE VS COLLECTION

STEP 1 - TABLE CREATE SCRIPTS:

QUICK LOOK AT THE RESULTS :

Temporary table : Elapsed: 00:00:17.84
Collections : Elapsed: 00:00:41.21

drop table coll_temp_source

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

drop table gtt_stage

CREATE GLOBAL TEMPORARY TABLE gtt_stage on commit preserve rows
as select a.rowid gtt_rid, a.* from coll_temp_source a where 1 = 2

create sequence dummy_seq1 start with 1

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 AS SELECT * FROM ALL_OBJECTS WHERE 1 = 2

STEP 2 - RUN COLLECTIONS SCRIPT

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
IF g_object_tab(i).owner = 'SYS' THEN
g_object_tab(i).secondary := 'Y';
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
g_object_tab(i).object_id := v_object_id;
END IF;

IF g_object_tab(i).object_type = 'JAVA CLASS' THEN
g_object_tab(i).temporary := 'Y';
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
g_object_tab(i).object_id := v_object_id;
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> @1_coll.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.21
SQL>


STEP 3 - RUN TEMPORARY TABLE BASED SCRIPT

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
TYPE rowid_list IS TABLE OF UROWID INDEX BY PLS_INTEGER;
min_rid_tab rowid_list;
max_rid_tab rowid_list;
g_error_level NUMBER;

BEGIN
g_error_level := 0;
INSERT INTO gtt_stage
SELECT /*+ ALL_ROWS */ ctgs.rowid rid
, ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, dummy_seq1.nextval obj_id
, ctgs.data_object_id data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM coll_temp_source ctgs ;

g_error_level := 2;
INSERT
INTO coll_temp_target5
SELECT /*+ ALL_ROWS */
ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.object_id obj_id
, ctgs.data_object_id data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, DECODE(object_type , 'JAVA CLASS', 'Y', ctgs.TEMPORARY) temp
, ctgs.GENERATED gen
, DECODE(ctgs.OWNER, 'SYS', 'Y', ctgs.SECONDARY) sec
FROM gtt_stage ctgs ;

g_error_level := 4;
COMMIT;

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> @l_temp.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.84
SQL>

*************************************************************************************