NORMAL INSERT VS MULTI-TABLE INSERT

Multi-table insert is introduced in ORACLE 9i. The main advantage is, it is simple to code and easy to maintain; just using a single INSERT script multiple tables and multiple data can be inserted into single / multiple tables.

1. Multi-table inserts is not possible across db links.
2. if you want to access sequence then it should be in the INSERT INTO clause and not in Select clause
3. FORALL can be used with Multi-table inserts
4. COLLECTIONS can be used in multi-table inserts
5. Insert-FIRST and Pivot table insert are also in the same lines.

My opinion - I prefer to use Multi-table inserts if possible in place of single table inserts.

STEP 1 - TABLE CREATE SCRIPTS:

QUICK LOOK AT THE RESULTS -

Multi-table Insert : Elapsed: 00:00:22.28
Normal Insert : Elapsed: 00:00:38.08

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_target;
drop table coll_temp_target2;
drop table coll_temp_target3;
drop table coll_temp_target4;

CREATE TABLE COLL_TEMP_TARGET
(OWNER VARCHAR2(30)
,OBJECT_NAME VARCHAR2(30)
,SUBOBJECT_NAME VARCHAR2(30)
,OBJECT_ID VARCHAR2(30));

CREATE TABLE COLL_TEMP_TARGET2
(OBJECT_ID NUMBER
,DATA_OBJECT_ID NUMBER
,OBJECT_TYPE VARCHAR2(18)
,CREATED DATE
,LAST_DDL_TIME DATE);

CREATE TABLE COLL_TEMP_TARGET3
(OBJECT_ID NUMBER
,TIMESTAMP VARCHAR2(19)
,STATUS VARCHAR2(7)
,TEMPORARY VARCHAR2(1));


CREATE TABLE COLL_TEMP_TARGET4
(OBJECT_ID NUMBER
,GENERATED VARCHAR2(1)
,SECONDARY VARCHAR2(1));

STEP 2 - MULTI-TABLE INSERT SCRIPT

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
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 ALL
WHEN 1 = 1 THEN
INTO coll_temp_target VALUES
(ownr, obj_nm, sub_obj, obj_id)
WHEN 1 = 1 THEN
INTO coll_temp_target2 VALUES
(obj_id, data_obj, obj_type, crtd, last_ddl)
WHEN 1 = 1 THEN
INTO coll_temp_target3 VALUES
(obj_id, time_stmp, stus, temp)
WHEN 1 = 1 THEN
INTO coll_temp_target4 VALUES
(obj_id, gen, sec)
SELECT
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
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM coll_temp_source ctgs ;

g_error_level := 2;
INSERT ALL
WHEN 1 = 1 THEN
INTO coll_temp_target VALUES
(ownr, obj_nm, sub_obj, obj_id)
WHEN 1 = 1 THEN
INTO coll_temp_target2 VALUES
(obj_id, data_obj, obj_type, crtd, last_ddl)
WHEN 1 = 1 THEN
INTO coll_temp_target3 VALUES
(obj_id, time_stmp, stus, temp)
WHEN 1 = 1 THEN
INTO coll_temp_target4 VALUES
(obj_id, gen, sec)
SELECT
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
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, 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> @1_temptab_multi.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.28

ONT TABLE INSERT SCRIPT

NOTES: -

STEP 3a - Single table Insert

truncate table coll_temp_target;
truncate table coll_temp_target2;
truncate table coll_temp_target3;
truncate table coll_temp_target4;

drop table coll_temp_source

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

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
, ctgs.OBJECT_ID obj_id
, dummy_seq1.nextval 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_target
SELECT
ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.OBJECT_ID obj_id
FROM gtt_stage ctgs ;


INSERT
INTO coll_temp_target2
SELECT
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
FROM gtt_stage ctgs ;


INSERT
INTO coll_temp_target3
SELECT
ctgs.OBJECT_ID obj_id
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
FROM gtt_stage ctgs ;

INSERT
INTO coll_temp_target4
SELECT
ctgs.OBJECT_ID obj_id
, ctgs.GENERATED gen
, 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> @1_temptab_onetable.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.08