Usually FORALL is possible only with DML operations (Insert / Update / Delete / Merge). From Oracle 9i it is possible with EXECUTE IMMEDIATE.
This is just for example purpose only - FORALL is really useful for bulk processing. Though in the below example a direct FORALL - INSERT is possible, have used EXECUTE IMMEDATE to demonstrate its use.
TABLE CREATION SCRIPTS
drop table coll_temp_source
CREATE TABLE coll_temp_source AS SELECT
status, object_id, owner
FROM ALL_OBJECTS where rownum < 201
drop table coll_temp_target
CREATE TABLE coll_temp_target AS SELECT
status, object_id, owner
FROM ALL_OBJECTS where rownum = 5
STEP 1 - USING FORALL
DECLARE
TYPE obj_list IS TABLE OF coll_temp_source%ROWTYPE;
obj_tab obj_list;
TYPE status_list IS TABLE OF coll_temp_source.status%TYPE;
status_tab status_list;
TYPE object_id_list IS TABLE OF coll_temp_source.object_id%TYPE;
object_id_tab object_id_list;
TYPE owner_list IS TABLE OF coll_temp_source.owner%TYPE;
owner_tab owner_list;
BEGIN
SELECT status, object_id, owner BULK COLLECT INTO status_tab, object_id_tab, owner_tab
FROM coll_temp_source WHERE rownum < 11;
FORALL i IN 1 .. status_tab.count
EXECUTE IMMEDIATE
'INSERT INTO coll_temp_target VALUES (:1, :2 ,:3)'
USING status_tab(i), object_id_tab(i) , owner_tab(i);
END;
/
STEP 2 - USING FOR.. LOOP
DECLARE
TYPE obj_list IS TABLE OF coll_temp_source%ROWTYPE;
obj_tab obj_list;
TYPE status_list IS TABLE OF coll_temp_source.status%TYPE;
status_tab status_list;
TYPE object_id_list IS TABLE OF coll_temp_source.object_id%TYPE;
object_id_tab object_id_list;
TYPE owner_list IS TABLE OF coll_temp_source.owner%TYPE;
owner_tab owner_list;
BEGIN
SELECT status, object_id, owner BULK COLLECT INTO status_tab, object_id_tab, owner_tab
FROM coll_temp_source WHERE rownum < 11;
FOR i IN 1 .. status_tab.count LOOP
EXECUTE IMMEDIATE
'INSERT INTO coll_temp_target VALUES (:1, :2 ,:3)'
USING status_tab(i), object_id_tab(i) , owner_tab(i);
END LOOP;
END;
/
SQL> @exec.sql
PL/SQL procedure successfully completed.
SQL> select * from coll_temp_target;
STATUS OBJECT_ID OWNER
------- ---------- ------------------------------
VALID 79400 SYS
VALID 69022 SYS
VALID 84837 SYS
VALID 75686 SYS
VALID 84402 SYS
VALID 71636 SYS
VALID 85043 SYS
VALID 85098 SYS
VALID 71312 SYS
VALID 73222 SYS
10 rows selected.
Friday, February 5, 2010
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>
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>
Wednesday, February 3, 2010
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.
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.
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>
*************************************************************************************
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>
*************************************************************************************
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
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
Few more examples - Analytical Functions
FINDING MISSING SEQUENCE / NUMBER from a table
SELECT /*+ ALL_ROWS */
pre_gap+1 start_gap, max_seq_gap
FROM
(SELECT /*+ ALL_ROWS */ seq_column post_gap, LAG(seq_column,1,0) OVER (ORDER BY seq_column) pre_gap ,
(SELECT /*+ ALL_ROWS */ MAX(seq_column) FROM table_name) max_seq_gap
FROM table_name)
WHERE pre_gap != 0
AND post_gap - pre_gap > 1
Delete duplicates
DELETE FROM table_name tnm WHERE tnm.rowid IN
(SELECT rowid FROM (SELECT ROWID, ROW_NUMBER () OVER (PARTITION BY ORDER BY ) duplicate FROM table_name ) qry
WHERE qry.duplicate > 1);
SELECT /*+ ALL_ROWS */
pre_gap+1 start_gap, max_seq_gap
FROM
(SELECT /*+ ALL_ROWS */ seq_column post_gap, LAG(seq_column,1,0) OVER (ORDER BY seq_column) pre_gap ,
(SELECT /*+ ALL_ROWS */ MAX(seq_column) FROM table_name) max_seq_gap
FROM table_name)
WHERE pre_gap != 0
AND post_gap - pre_gap > 1
Delete duplicates
DELETE FROM table_name tnm WHERE tnm.rowid IN
(SELECT rowid FROM (SELECT ROWID, ROW_NUMBER () OVER (PARTITION BY
WHERE qry.duplicate > 1);
Not Exists vs. Analytical functions
Analytical Functions - Introduced in Oracle 8i. There are different families of functions available and with each release new functions are introduced to the family tree. The below is a quick demonstration of using Analytical functions in place of NOT EXISTS.
Note: Similar logic can be applied to EXISTS also.
This is particularly useful for huge sets of data joined across multiple tables.
Following is a simple demonstration.
Case 1 - NOT EXIST vs USE OF ANALYTICAL FUNCTION
Requirement - Pick only those objects which are referenced in - PACKAGE, PACKAGE BODY, INDEX, TABLE, FUNCTION, PROCEDURE.
and not part of the rest.
ie., say,
TABLE_NAME OBJECT_TYPE
TAB1 PACKAGE
TAB1 PACKAGE BODY
TAB1 [ TRIGGER ]
TAB1 INDEX
Say, in the above example TRIGGER is not part of the expected in-list [ PACKAGE, PACKAGE BODY, INDEX, TABLE, FUNCTION, PROCEDURE ]. So, this should not be taken for consideration.
TABLE_NAME OBJECT_TYPE
TAB2 PACKAGE
TAB2 PACKAGE BODY
TAB2 INDEX
Above is part of the in-list, it should be included to the select.
Step 1 - Create table
Drop table KEY_TABLE
CREATE TABLE KEY_TABLE (table_name varchar2(50), referenced_in varchar2(50), object_id number)
Step 2 - Populate data
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB1', object_type
from all_objects) a
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB2', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
) a
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB3', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'PACKAGE BODY''PROCEDURE')
) a
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB4', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'PACKAGE BODY''PROCEDURE', 'TRIGGER')
) a
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB5', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE BODY', 'PACKAGE', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
) a
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct
'TAB'||rownum * 20
, object_type
from all_objects) a
Step 3 - QUERY
-- USING NOT EXISTS
SELECT /*+ ALL_ROWS */ * from key_table ktab
WHERE ktab.REFERENCEd_IN in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
AND NOT EXISTS
(SELECT 1 FROM key_Table elim
WHERE elim.object_id = ktab.object_id
AND elim.REFERENCEd_IN not in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE'))
-- USING ANALYTICAL FUNCTIONS
SELECT /*+ ALL_ROWS */ ktab.*,
COUNT(1) OVER (PARTITION BY referenced_in, table_name order by referenced_in, table_name) COUNT_TOTAL,
(CASE WHEN ktab.referenced_in in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE') THEN
1
else
0
END) COUNT_ELIM
FROM key_table_1 ktab
Note: Similar logic can be applied to EXISTS also.
This is particularly useful for huge sets of data joined across multiple tables.
Following is a simple demonstration.
Case 1 - NOT EXIST vs USE OF ANALYTICAL FUNCTION
Requirement - Pick only those objects which are referenced in - PACKAGE, PACKAGE BODY, INDEX, TABLE, FUNCTION, PROCEDURE.
and not part of the rest.
ie., say,
TABLE_NAME OBJECT_TYPE
TAB1 PACKAGE
TAB1 PACKAGE BODY
TAB1 [ TRIGGER ]
TAB1 INDEX
Say, in the above example TRIGGER is not part of the expected in-list [ PACKAGE, PACKAGE BODY, INDEX, TABLE, FUNCTION, PROCEDURE ]. So, this should not be taken for consideration.
TABLE_NAME OBJECT_TYPE
TAB2 PACKAGE
TAB2 PACKAGE BODY
TAB2 INDEX
Above is part of the in-list, it should be included to the select.
Step 1 - Create table
Drop table KEY_TABLE
CREATE TABLE KEY_TABLE (table_name varchar2(50), referenced_in varchar2(50), object_id number)
Step 2 - Populate data
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB1', object_type
from all_objects) a
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB2', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
) a
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB3', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'PACKAGE BODY''PROCEDURE')
) a
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB4', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'PACKAGE BODY''PROCEDURE', 'TRIGGER')
) a
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB5', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE BODY', 'PACKAGE', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
) a
INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct
'TAB'||rownum * 20
, object_type
from all_objects) a
Step 3 - QUERY
-- USING NOT EXISTS
SELECT /*+ ALL_ROWS */ * from key_table ktab
WHERE ktab.REFERENCEd_IN in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
AND NOT EXISTS
(SELECT 1 FROM key_Table elim
WHERE elim.object_id = ktab.object_id
AND elim.REFERENCEd_IN not in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE'))
-- USING ANALYTICAL FUNCTIONS
SELECT /*+ ALL_ROWS */ ktab.*,
COUNT(1) OVER (PARTITION BY referenced_in, table_name order by referenced_in, table_name) COUNT_TOTAL,
(CASE WHEN ktab.referenced_in in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE') THEN
1
else
0
END) COUNT_ELIM
FROM key_table_1 ktab
Subscribe to:
Posts (Atom)