Author: Paul Turner

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. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   A Guideline for Oracle Instantiation with RMAN!
   What if your database lock gets blocked??
   Step by Step Guide to Oracle Parsing
   Beware of Worse Oracle performance after Migration!!