MERGE BASED ON TEMPORARY TABLES VS MERGE BASED ON COLLECTIONS

Merge - It is always better than separate INSERT / UPDATE and DELETE operations (Delete operation is added in 11g). FORALL Merge is pretty much possible while using COLLECTIONS. FORALL Merge is obviously faster than FOR LOOP Merge, as it enables bulk processing.

My opinion - If there is a choice between FORALL Merge and Merge using Global Temporary tables, its better to use the later. Following is a simple demonstration.

STEP 1 - TABLE CREATE SCRIPTS:

QUICK LOOK AT RESULTS -

Merge based on Temporary table : Elapsed: 00:00:35.68
Merge based on Collections : Elapsed: 00:11:20.27
Merge based on actual table : Elapsed: 00:00:37.30

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
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source where rownum < 20000.


STEP 2 - MERGE + TEMP TABLE 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
, ctgs.OBJECT_NAME
, ctgs.SUBOBJECT_NAME
, rownum
, ctgs.data_object_id
, ctgs.OBJECT_TYPE
, ctgs.CREATED
, ctgs.LAST_DDL_TIME
, ctgs.TIMESTAMP
, ctgs.STATUS
, ctgs.TEMPORARY
, ctgs.GENERATED
, ctgs.SECONDARY
FROM coll_temp_source ctgs ORDER BY ROWID;

g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO min_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm,
ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 1
ORDER BY 1;

g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO max_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm, ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 0
UNION
SELECT /*+ ALL_ROWS */ MAX (ROWID)
FROM coll_temp_source;

g_error_level := 2;
FORALL i IN min_rid_tab.FIRST .. min_rid_tab.LAST
MERGE INTO coll_temp_target5 tgt
USING
(SELECT /*+ INDEX (ctgs, gtt_stage_idx) */
ctgs.OWNER OWNER
, ctgs.OBJECT_NAME OBJECT_NAME
, ctgs.SUBOBJECT_NAME SUBOBJECT_NAME
, ctgs.object_id object_id
, ctgs.data_object_id data_object_id
, ctgs.OBJECT_TYPE OBJECT_TYPE
, ctgs.CREATED CREATED
, ctgs.LAST_DDL_TIME LAST_DDL_TIME
, ctgs.TIMESTAMP TIMESTAMP
, ctgs.STATUS STATUS
, DECODE(object_type , 'JAVA CLASS', 'Y', ctgs.TEMPORARY) TEMPORARY
, ctgs.GENERATED GENERATED
, DECODE(ctgs.OWNER, 'SYS', 'Y', ctgs.SECONDARY) SECONDARY
FROM gtt_stage ctgs WHERE gtt_rid BETWEEN min_rid_tab(i) AND max_rid_tab(i)) src
ON (tgt.object_id = src.object_id)
WHEN MATCHED THEN
UPDATE SET
tgt.OWNER = src.OWNER
,tgt.OBJECT_NAME = src.OBJECT_NAME
,tgt.SUBOBJECT_NAME = src.SUBOBJECT_NAME
,tgt.data_object_id = src.data_object_id
,tgt.OBJECT_TYPE = src.OBJECT_TYPE
,tgt.CREATED = src.CREATED
,tgt.LAST_DDL_TIME = src.LAST_DDL_TIME
,tgt.TIMESTAMP = src.TIMESTAMP
,tgt.STATUS = src.STATUS
,tgt.TEMPORARY = src.TEMPORARY
,tgt.GENERATED = src.GENERATED
,tgt.SECONDARY = src.SECONDARY
WHEN NOT MATCHED THEN
INSERT
( tgt.OWNER
,tgt.OBJECT_NAME
,tgt.SUBOBJECT_NAME
,tgt.object_id
,tgt.data_object_id
,tgt.OBJECT_TYPE
,tgt.CREATED
,tgt.LAST_DDL_TIME
,tgt.TIMESTAMP
,tgt.STATUS
,tgt.TEMPORARY
,tgt.GENERATED
,tgt.SECONDARY )
VALUES
(
src.OWNER
,src.OBJECT_NAME
,src.SUBOBJECT_NAME
,src.object_id
,src.data_object_id
,src.OBJECT_TYPE
,src.CREATED
,src.LAST_DDL_TIME
,src.TIMESTAMP
,src.STATUS
,src.TEMPORARY
,src.GENERATED
,src.SECONDARY
);


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

PL/SQL procedure successfully completed.

Elapsed: 00:00:35.68
SQL>



STEP 3 - MERGE + COLLECTIONS Scripts

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 as
select
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source where rownum < 20000


SET TIMING ON
SET SERVEROUTPUT ON SIZE 10000

DECLARE
TYPE g_OWNER IS TABLE OF COLL_TEMP_SOURCE.OWNER%type;
TYPE g_OBJECT_NAME IS TABLE OF COLL_TEMP_SOURCE.OBJECT_NAME%type;
TYPE g_SUBOBJECT_NAME IS TABLE OF COLL_TEMP_SOURCE.SUBOBJECT_NAME%type;
TYPE g_OBJECT_ID IS TABLE OF COLL_TEMP_SOURCE.OBJECT_ID%type;
TYPE g_DATA_OBJECT_ID IS TABLE OF COLL_TEMP_SOURCE.DATA_OBJECT_ID%type;
TYPE g_OBJECT_TYPE IS TABLE OF COLL_TEMP_SOURCE.OBJECT_TYPE%type;
TYPE g_CREATED IS TABLE OF COLL_TEMP_SOURCE.CREATED%type;
TYPE g_LAST_DDL_TIME IS TABLE OF COLL_TEMP_SOURCE.LAST_DDL_TIME%type;
TYPE g_TIMESTAMP IS TABLE OF COLL_TEMP_SOURCE.TIMESTAMP%type;
TYPE g_STATUS IS TABLE OF COLL_TEMP_SOURCE.STATUS%type;
TYPE g_TEMPORARY IS TABLE OF COLL_TEMP_SOURCE.TEMPORARY%type;
TYPE g_GENERATED IS TABLE OF COLL_TEMP_SOURCE.GENERATED%type;
TYPE g_SECONDARY IS TABLE OF COLL_TEMP_SOURCE.SECONDARY%type;

g_OWNER_t g_OWNER;
g_OBJECT_NAME_t g_OBJECT_NAME;
g_SUBOBJECT_NAME_t g_SUBOBJECT_NAME;
g_OBJECT_ID_t g_OBJECT_ID;
g_DATA_OBJECT_ID_t g_DATA_OBJECT_ID;
g_OBJECT_TYPE_t g_OBJECT_TYPE;
g_CREATED_t g_CREATED;
g_LAST_DDL_TIME_t g_LAST_DDL_TIME;
g_TIMESTAMP_t g_TIMESTAMP;
g_STATUS_t g_STATUS;
g_TEMPORARY_t g_TEMPORARY;
g_GENERATED_t g_GENERATED;
g_SECONDARY_t g_SECONDARY;


g_error_level NUMBER;
v_object_id NUMBER;

CURSOR cur_coll_temp IS
SELECT
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
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_OWNER_t
,g_OBJECT_NAME_t
,g_SUBOBJECT_NAME_t
,g_OBJECT_ID_t
,g_DATA_OBJECT_ID_t
,g_OBJECT_TYPE_t
,g_CREATED_t
,g_LAST_DDL_TIME_t
,g_TIMESTAMP_t
,g_STATUS_t
,g_TEMPORARY_t
,g_GENERATED_t
,g_SECONDARY_t LIMIT 300;

g_error_level := 3;
EXIT WHEN cur_coll_temp%NOTFOUND;

FORALL i IN g_owner_t.FIRST .. g_owner_t.LAST
MERGE INTO coll_temp_target5 tgt
USING
(SELECT /*+ ALL_ROWS */
g_OWNER_t(i) owner
,g_OBJECT_NAME_t(i) object_name
,g_SUBOBJECT_NAME_t(i) subobject_name
,g_OBJECT_ID_t(i) object_id
,g_DATA_OBJECT_ID_t(i) data_object_id
,g_OBJECT_TYPE_t(i) object_type
,g_CREATED_t(i) created
,g_LAST_DDL_TIME_t(i) last_ddl_time
,g_TIMESTAMP_t(i) timestamp
,g_STATUS_t(i) status
, DECODE(g_OBJECT_TYPE_t(i) , 'JAVA CLASS', 'Y', g_TEMPORARY_t(i)) TEMPORARY
,g_GENERATED_t(i) generated
,DECODE(g_OWNER_t(i), 'SYS', 'Y', g_SECONDARY_t(i)) secondary
FROM dual ) src
ON (tgt.object_id = src.object_id)
WHEN MATCHED THEN
UPDATE SET
tgt.OWNER = src.OWNER
,tgt.OBJECT_NAME = src.OBJECT_NAME
,tgt.SUBOBJECT_NAME = src.SUBOBJECT_NAME
,tgt.data_object_id = src.data_object_id
,tgt.OBJECT_TYPE = src.OBJECT_TYPE
,tgt.CREATED = src.CREATED
,tgt.LAST_DDL_TIME = src.LAST_DDL_TIME
,tgt.TIMESTAMP = src.TIMESTAMP
,tgt.STATUS = src.STATUS
,tgt.TEMPORARY = src.TEMPORARY
,tgt.GENERATED = src.GENERATED
,tgt.SECONDARY = src.SECONDARY
WHEN NOT MATCHED THEN
INSERT
(tgt.OWNER
,tgt.OBJECT_NAME
,tgt.SUBOBJECT_NAME
,tgt.object_id
,tgt.data_object_id
,tgt.OBJECT_TYPE
,tgt.CREATED
,tgt.LAST_DDL_TIME
,tgt.TIMESTAMP
,tgt.STATUS
,tgt.TEMPORARY
,tgt.GENERATED
,tgt.SECONDARY )
VALUES
(
src.OWNER
,src.OBJECT_NAME
,src.SUBOBJECT_NAME
,src.object_id
,src.data_object_id
,src.OBJECT_TYPE
,src.CREATED
,src.LAST_DDL_TIME
,src.TIMESTAMP
,src.STATUS
,src.TEMPORARY
,src.GENERATED
,src.SECONDARY
);

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> @merge_coll.sql;


Elapsed: 00:11:20.27

Cancelled the run -
Stopped after processing 58800 760475 records.

STEP 4 - MERGE + TABLE Script

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 as
select
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source where rownum < 20000


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 := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO min_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm,
ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 1
ORDER BY 1;

g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO max_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm, ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 0
UNION
SELECT /*+ ALL_ROWS */ MAX (ROWID)
FROM coll_temp_source;

g_error_level := 2;
FORALL i IN min_rid_tab.FIRST .. min_rid_tab.LAST
MERGE INTO coll_temp_target5 tgt
USING
(SELECT /*+ ALL_ROWS */
ctgs.OWNER OWNER
, ctgs.OBJECT_NAME OBJECT_NAME
, ctgs.SUBOBJECT_NAME SUBOBJECT_NAME
, ctgs.object_id object_id
, ctgs.data_object_id data_object_id
, ctgs.OBJECT_TYPE OBJECT_TYPE
, ctgs.CREATED CREATED
, ctgs.LAST_DDL_TIME LAST_DDL_TIME
, ctgs.TIMESTAMP TIMESTAMP
, ctgs.STATUS STATUS
, DECODE(object_type , 'JAVA CLASS', 'Y', ctgs.TEMPORARY) TEMPORARY
, ctgs.GENERATED GENERATED
, DECODE(ctgs.OWNER, 'SYS', 'Y', ctgs.SECONDARY) SECONDARY
FROM coll_temp_source ctgs WHERE rowid BETWEEN min_rid_tab(i) AND max_rid_tab(i)) src
ON (tgt.object_id = src.object_id)
WHEN MATCHED THEN
UPDATE SET
tgt.OWNER = src.OWNER
,tgt.OBJECT_NAME = src.OBJECT_NAME
,tgt.SUBOBJECT_NAME = src.SUBOBJECT_NAME
,tgt.data_object_id = src.data_object_id
,tgt.OBJECT_TYPE = src.OBJECT_TYPE
,tgt.CREATED = src.CREATED
,tgt.LAST_DDL_TIME = src.LAST_DDL_TIME
,tgt.TIMESTAMP = src.TIMESTAMP
,tgt.STATUS = src.STATUS
,tgt.TEMPORARY = src.TEMPORARY
,tgt.GENERATED = src.GENERATED
,tgt.SECONDARY = src.SECONDARY
WHEN NOT MATCHED THEN
INSERT
( tgt.OWNER
,tgt.OBJECT_NAME
,tgt.SUBOBJECT_NAME
,tgt.object_id
,tgt.data_object_id
,tgt.OBJECT_TYPE
,tgt.CREATED
,tgt.LAST_DDL_TIME
,tgt.TIMESTAMP
,tgt.STATUS
,tgt.TEMPORARY
,tgt.GENERATED
,tgt.SECONDARY )
VALUES
(
src.OWNER
,src.OBJECT_NAME
,src.SUBOBJECT_NAME
,src.object_id
,src.data_object_id
,src.OBJECT_TYPE
,src.CREATED
,src.LAST_DDL_TIME
,src.TIMESTAMP
,src.STATUS
,src.TEMPORARY
,src.GENERATED
,src.SECONDARY
);


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

PL/SQL procedure successfully completed.

Elapsed: 00:00:37.30
SQL>



Notes -
1. Merge based on TEMP table is way faster for huge data set !!
2. FORALL is possible for MERGE statement
3. Collections can be used in MERGE - its ok for small sets of data. Use global temporary for large sets of data.

The above example for temporary table picks small chunks of data and processes the same. This is achieved by referencing ROWID.