Oracle Multitable Inserts

Oracle Multitable Inserts, Important Concerns!!
All database administrators and developers encounter single table INSERT statements
every now or then. Oracle also supports a multiple-table INSERT statement that enables
you to make a single pass through the source data and load the data into more than one
table.
Oracle multitable Insert feature has been available since Oracle since 9i. There are two
types of multi-table insert: simple multitable insert and conditional multitable insert.
Simple multitable insert will insert values into all of the tables whereas conditional
multitable insert insert rows into some of the tables instead of all of them.
Oracle Multitable Insert Syntax:
The syntax of Oracle multitable Inserts is as follows
INSERT [ ALL FIRST ]
WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
[WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]...
[ELSE insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]
The `ALL` keyword makes database check each WHEN condition whereas the `FIRST`
keyword makes database to stop checking the WHEN conditions once first TRUE
condition is met. If WHEN condition is TRUE then the corresponding INTO clause will
be executed. However, if no WHEN condition is TRUE then ELSE clause will be
executed. The INSERT ALL statement allows insertion of several different values clauses
in a single statement.
Simple Multitable Insert:
create table MYTABLE1 (COL1 NUMBER(30), COL2 NUMBER(30) );
Table created.
create table MYTABLE2 (COL3 NUMBER(30), COL4 DATE);
Table created.
CREATE SEQUENCE MYSEQ INCREMENT BY 1;
Sequence created.
INSERT ALL
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID )
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID+1 )
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID+2 )
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID+3 )
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID +4)
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID +5)
select MYID, created from all_objects
/
Conditional Multitable Insert:
create table MYTABLE1 (COL1 NUMBER(30), COL2 NUMBER(30) );
Table created.
create table MYTABLE2 (COL3 NUMBER(30), COL4 DATE);
Table created.
CREATE SEQUENCE MYSEQ INCREMENT BY 1;
Sequence created.
insert
when mod( MYID, 2 ) = 1 then
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID )
when mod( MYID, 2 ) = 0 then
into MYTABLE2 ( COL3, COL4 ) values ( MYSEQ.nextval, created )
select MYID, created from all_objects
/
Multitable INSERT statement increases performance as the code will do a single pass
through the all_objects table instead of two. Hence your data will be loaded faster.
If you had used single table insert then you would be subject to cost of SELECT
statement twice; once for each insert into…select statement. The SELECT statement will
be run only once in a multi-table insert and therefore you will be subject to the cost of
SELECT statement only.
Limitations of Oracle Multitable Inserts:
Apart from its beauty there are some limitations of Oracle multitable inserts. Oracle
multitable inserts can be performed only on tables. You can not attempt multitable inserts
on views, materialized views or on a remote table. All of insert_into_clause s cannot
combine to specify more than 999 target columns in Oracle multitable insert. Oracle does
not guarantee the order of inserts in spite of the explicit ordering in the select clause.
While performing a multitable insert, a table collection expression can not be specified.
Also a subquery of the multitable insert statement cannot use a sequence.
Multitable inserts are not parallelized in RAC environment. Similarly if your target table
is index organized or has a bitmap index defined on it, then also multiple inserts are not
parallelized. Oracle multitable insert statements does not support plan stability.