Oracle Illustrated

Feb 21 2010   9:15AM GMT

Performance Tuning – TEMPORARY TABLE VS COLLECTION

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

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>

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: