multi-table inserts in oracle 9i
Multi-table insert is a new feature of Oracle 9i Release 1 (9.0). An extension to INSERT..SELECT, this feature enables us to define multiple insert targets for a source dataset. Until the introduction of this feature, only SQL*Loader had a similar capability.
This article provides an overview of multi-table inserts and how they are used.
syntax overview
There are two types of multi-table insert as follows:
- INSERT FIRST; and
- INSERT ALL.
Multi-table inserts are an extension to INSERT..SELECT. Syntax is of the following form:
INSERT ALL|FIRST [WHEN condition THEN] INTO target [VALUES] [WHEN condition THEN] INTO target [VALUES] ... [ELSE] INTO target [VALUES] SELECT ... FROM source_query;
We define multiple INTO targets between the INSERT ALL/FIRST and the SELECT. The inserts can be conditional or unconditional and if the record structure of the datasource matches the target table, the VALUES clause can be omitted. We will describe the various permutations in this article.
setup
For the examples in this article, we shall use the ALL_OBJECTS view as our source data. For simplicity, we will create four tables with the same structure as follows.
SQL> CREATE TABLE t1 2 ( owner VARCHAR2(30) 3 , object_name VARCHAR2(30) 4 , object_type VARCHAR2(30) 5 , object_id NUMBER 6 , created DATE 7 );
Table created.
SQL> CREATE TABLE t2 AS SELECT * FROM t1;
Table created.
SQL> CREATE TABLE t3 AS SELECT * FROM t1;
Table created.
SQL> CREATE TABLE t4 AS SELECT * FROM t1;
Table created.
These tables will be our targets for the ALL_OBJECTS view data.
simple multi-table insert
To begin, we will unconditionally INSERT ALL the source data into every target table. The source records and target tables are all of the same structure so we will omit the VALUES clause from each INSERT.
SQL> SELECT COUNT(*) FROM all_objects;
COUNT(*)
----------
28981
1 row selected.
SQL> INSERT ALL 2 INTO t1 3 INTO t2 4 INTO t3 5 INTO t4 6 SELECT owner 7 , object_type 8 , object_name 9 , object_id 10 , created 11 FROM all_objects;
115924 rows created.
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
----------
28981
1 row selected.
Note the feedback from sqlplus and compare this to the count of ALL_OBJECTS. We get the total number of records inserted and this is evenly distributed between our target tables (although in practice, this will usually be distributed unevenly between the target tables).
Before we continue with extended syntax, note that multi-table inserts can turn single source records into multiple target records (i.e. to re-direct portions of records to different tables). We can see this in the previous example where we insert four times the number of source records. We can also generate multiple records for a single table (i.e. the same table is repeatedly used as a target) whereby each record picks a different set of attributes from the source record (similar to pivotting).
conditional multi-table insert
Multi-table inserts can also be conditional (i.e. we do not need to insert every record into every table in the list). There are some key points to note about conditional multi-table inserts as follows.
- we cannot mix conditional with unconditional inserts. This means that in situations where we need a conditional insert on a subset of target tables, we will often need to "pad out" the unconditional inserts with a dummy condition such as "WHEN 1=1";
- we can optionally include an ELSE clause in our INSERT ALL|FIRST target list for when none of the explicit conditions are satisfied;
- an INSERT ALL conditional statement will evaluate every insert condition for each record. With INSERT FIRST, each record will stop being evaluated on the first condition it satisfies;
- the conditions in an INSERT FIRST statement will be evaluated in order from top to bottom. Oracle makes no such guarantees with an INSERT ALL statement.
With these restrictions in mind, we can now see an example of a conditional INSERT FIRST statement. Each source record will be directed to one target table at most. Note that for demonstration purposes, the following example includes varying column lists and an ELSE clause.
SQL> INSERT FIRST 2 --<>-- 3 WHEN owner = 'SYSTEM' 4 THEN 5 INTO t1 (owner, object_name) 6 VALUES (owner, object_name) 7 --<>-- 8 WHEN object_type = 'TABLE' 9 THEN 10 INTO t2 (owner, object_name, object_type) 11 VALUES (owner, object_name, object_type) 12 --<>-- 13 WHEN object_name LIKE 'DBMS%' 14 THEN 15 INTO t3 (owner, object_name, object_type) 16 VALUES (owner, object_name, object_type) 17 --<>-- 18 ELSE 19 INTO t4 (owner, object_type, object_name, created, object_id) 20 VALUES (owner, object_type, object_name, created, object_id) 21 --<>-- 22 SELECT owner 23 , object_type 24 , object_name 25 , object_id 26 , created 27 FROM all_objects;
28981 rows created.
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
----------
362
1 row selected.
SQL> SELECT COUNT(*) FROM t2;
COUNT(*)
----------
844
1 row selected.
SQL> SELECT COUNT(*) FROM t3;
COUNT(*)
----------
266
1 row selected.
SQL> SELECT COUNT(*) FROM t4;
COUNT(*)
----------
27509
1 row selected.
We can see that each source record was inserted into one table only. INSERT FIRST is a good choice for performance when each source record is intended for one target only, but in practice, INSERT ALL is much more common.
Remember that we cannot mix conditional with unconditional inserts. The following example shows the unintuitive error message we receive if we try.
SQL> INSERT ALL 2 --<>-- 3 INTO t1 (owner, object_name) --<-- unconditional 4 VALUES (owner, object_name) 5 --<>-- 6 WHEN object_type = 'TABLE' --<-- conditional 7 THEN 8 INTO t2 (owner, object_name, object_type) 9 VALUES (owner, object_name, object_type) 10 --<>-- 11 SELECT owner 12 , object_type 13 , object_name 14 , object_id 15 , created 16 FROM all_objects;
INTO t1 (owner, object_name) --<-- unconditional * ERROR at line 3: ORA-00905: missing keyword
The workaround to this, as stated earlier, is to include a dummy TRUE condition as follows.
SQL> INSERT ALL 2 --<>-- 3 WHEN 1 = 1 --<-- dummy TRUE condition 4 THEN 5 INTO t1 (owner, object_name) 6 VALUES (owner, object_name) 7 --<>-- 8 WHEN object_type = 'TABLE' --<-- conditional 9 THEN 10 INTO t2 (owner, object_name, object_type) 11 VALUES (owner, object_name, object_type) 12 --<>-- 13 SELECT owner 14 , object_type 15 , object_name 16 , object_id 17 , created 18 FROM all_objects;
29958 rows created.
Counter-intuitive to this is the fact that in a conditional multi-table insert, each INTO clause inherits the current condition until it changes. We can see this below by loading T1, T2 and T3 from a single condition in an INSERT ALL statement. The T4 table will be loaded from the ELSE clause.
SQL> INSERT ALL 2 WHEN owner = 'SYSTEM' 3 THEN 4 INTO t1 (owner, object_name) 5 VALUES (owner, object_name) 6 --<>-- 7 INTO t2 (owner, object_name, object_type) --<-- owner = 'SYSTEM 8 VALUES (owner, object_name, object_type) 9 --<>-- 10 INTO t3 (owner, object_name, object_type) --<-- owner = 'SYSTEM 11 VALUES (owner, object_name, object_type) 12 ELSE 13 INTO t4 (owner, object_type, object_name, created, object_id) 14 VALUES (owner, object_type, object_name, created, object_id) 15 SELECT owner 16 , object_type 17 , object_name 18 , object_id 19 , created 20 FROM all_objects;
29705 rows created.
SQL> SELECT COUNT(*) FROM all_objects WHERE owner = 'SYSTEM';
COUNT(*)
----------
362
1 row selected.
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
----------
362
1 row selected.
SQL> SELECT COUNT(*) FROM t2;
COUNT(*)
----------
362
1 row selected.
SQL> SELECT COUNT(*) FROM t3;
COUNT(*)
----------
362
1 row selected.
multi-table inserts and triggers
As we might expect, multi-table inserts will cause insert-event triggers to fire. We can see this quite simply with the following example. We create two insert triggers (one for T1 and one for T2) and run a conditional INSERT ALL statement. Each trigger will output a message to the screen on firing.
SQL> CREATE OR REPLACE TRIGGER t1_insert_trigger
2 AFTER INSERT ON t1
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('T1 trigger fired...');
5 END insert_trigger;
6 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER t2_insert_trigger
2 AFTER INSERT ON t2
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('T2 trigger fired...');
5 END insert_trigger;
6 /
Trigger created.
SQL> INSERT ALL 2 --<>-- 3 WHEN owner = 'SYSTEM' 4 THEN 5 INTO t1 (owner, object_name) 6 VALUES (owner, object_name) 7 --<>-- 8 WHEN object_type = 'TABLE' 9 THEN 10 INTO t2 (owner, object_name, object_type) 11 VALUES (owner, object_name, object_type) 12 --<>-- 13 SELECT owner 14 , object_type 15 , object_name 16 FROM all_objects;
T1 trigger fired... T2 trigger fired... 1339 rows created.
multi-table inserts and sequences
Sequences can be used directly in multi-table inserts but their placement can be counter-intuitive. They are referenced in the relevant VALUES clause(s) and not in the source query (as we might expect). Further to this, when referencing a single sequence in multiple VALUES clauses, we might consider it necessary to be "smart" with our use of the NEXTVAL and CURRVAL pseudo-columns. This is not the case, as the following example demonstrates. We will create a sequence and use it in multiple INTO..VALUES clauses.
SQL> CREATE SEQUENCE multi_table_seq;
Sequence created.
SQL> INSERT ALL 2 --<>-- 3 INTO t1 (owner, object_id) 4 VALUES (owner, multi_table_seq.NEXTVAL) 5 --<>-- 6 INTO t1 (owner, object_id) 7 VALUES (owner, multi_table_seq.NEXTVAL) 8 --<>-- 9 INTO t1 (owner, object_id) 10 VALUES (owner, multi_table_seq.NEXTVAL) 11 --<>-- 12 INTO t1 (owner, object_id) 13 VALUES (owner, multi_table_seq.NEXTVAL) 14 --<>-- 15 SELECT owner 16 , object_type 17 , object_name 18 , object_id 19 , created 20 FROM all_objects 21 WHERE ROWNUM <= 50; 200 rows created. SQL> SELECT COUNT(*) AS record_count 2 , COUNT(DISTINCT(object_id)) AS sequence_numbers_assigned 3 FROM t1;
RECORD_COUNT SEQUENCE_NUMBERS_ASSIGNED
------------ -------------------------
200 50
1 row selected.
We can see from this example that the sequence.NEXTVAL expression is used in each VALUES clause but each increment is constant for the entire INTO list. Given the fact that Oracle doesn't guarantee the execution order of an INSERT ALL statement, this sequence behaviour actually makes sense (because we couldn't guarantee a NEXTVAL before a CURRVAL, for example).
multi-table inserts and referential constraints
It has been stated that Oracle does not guarantee the insert order of an INSERT ALL statement, despite the fact that we will usually observe an ordered behaviour. This fact is critical when we have a set of INTO target tables that have parent-child relationships between them. We might consider that simply ordering the INTO clauses in a way such that the parent is inserted before the child is sufficient. Fortunately, most of the time this will be the case but Oracle cannot guarantee it. This author has had to use deferrable constraints to workaround this issue in a large, six-table parallel insert where the INTO ordering was not maintained. Reproducing this problem for this article, however, has not been possible but it is important that we are aware of the potential issue.
performance considerations
The performance of multi-table inserts can be improved in several ways.
First, we can use INSERT FIRST if it makes sense to do so and if the insert volumes are high (though in practice this will make only a small difference).
Second, we can use hints such as PARALLEL or APPEND for "brute-force" loading in parallel or direct-path. Hints are added between the INSERT and the ALL/FIRST as follows:
INSERT /*+ hint */ ALL|FIRST
With regard to parallel insert, the documentation states that the entire statement will be parallelised if we use a PARALLEL hint for any of the target tables (even if the target tables haven't been created with PARALLEL). If no hint is supplied, then the insert will not be performed in parallel unless every table in the statement has its PARALLEL attribute set.
Third, we can tune the source query as this is likely to be the most "expensive" part of the operation. The benefit of multi-table inserts over pre-9i solutions is that we need only generate the source dataset once. Of course, large SQL statements can often provide numerous opportunities for tuning, so we can benefit in two ways: once to reduce the work to a single statement; and twice to tune the single statement itself.
We can compare a multi-table insert with a pre-9i solution of loading each table separately. We will load the ALL_OBJECTS source data into our four target tables, first with multi-table insert (INSERT ALL) and second as four separate statements. We will use a variation on Tom Kyte's RUNSTATS utility to measure the time and resource differences between the two methods.
We will begin with the multi-table solution.
SQL> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> INSERT ALL 2 INTO t1 3 INTO t2 4 INTO t3 5 INTO t4 6 SELECT owner 7 , object_type 8 , object_name 9 , object_id 10 , created 11 FROM all_objects;
115924 rows created.
Now we can run the pre-9i solution by executing four separate statements.
SQL> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> INSERT INTO t1 2 SELECT owner, object_name, object_type, object_id, created 3 FROM all_objects;
28981 rows created.
SQL> INSERT INTO t2 2 SELECT owner, object_name, object_type, object_id, created 3 FROM all_objects;
28981 rows created.
SQL> INSERT INTO t3 2 SELECT owner, object_name, object_type, object_id, created 3 FROM all_objects;
28981 rows created.
SQL> INSERT INTO t4 2 SELECT owner, object_name, object_type, object_id, created 3 FROM all_objects;
28981 rows created.
Finally we can report the time and resource differences as follows.
SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 233 hsecs Run2 ran in 663 hsecs Run1 ran in 35.14% of the time Name Run1 Run2 Diff STAT..bytes received via SQL*N 1,010 2,453 1,443 STAT..dirty buffers inspected 0 1,536 1,536 STAT..free buffer inspected 0 1,537 1,537 STAT..hot buffers moved to hea 0 2,194 2,194 LATCH.checkpoint queue latch 73 4,047 3,974 STAT..index fetch by key 1,496 5,952 4,456 STAT..rows fetched via callbac 1,488 5,952 4,464 LATCH.cache buffers lru chain 89 4,731 4,642 STAT..consistent gets - examin 3,899 12,816 8,917 LATCH.simulator hash latch 6,396 25,049 18,653 STAT..redo size 7,444,524 7,421,848 -22,676 STAT..index scans kdiixs1 44,658 178,600 133,942 STAT..table fetch by rowid 47,796 191,184 143,388 STAT..buffer is not pinned cou 70,673 282,660 211,987 STAT..buffer is pinned count 71,067 284,268 213,201 STAT..no work - consistent rea 98,650 394,552 295,902 STAT..session logical reads 107,016 411,832 304,816 STAT..consistent gets 103,457 408,276 304,819 LATCH.cache buffers chains 220,008 826,083 606,075 Run1 latches total versus run2 -- difference and pct Run1 Run2 Diff Pct 234,021 868,611 634,590 26.94% PL/SQL procedure successfully completed.
We can see that the multi-table insert is considerably quicker in our example. This is because the cost of generating the source dataset is borne only once with this solution. The overall level of resources used by the inserts are very similar (i.e. we write the same volume of data regardless of the approach).
multi-table insert restrictions
There are several restrictions with multi-table inserts. The online documentation lists the following:
- we cannot have views or materialized views as targets;
- we cannot use remote tables as targets;
- we cannot load more than 999 columns (all INTO clause combined);
- we cannot parallel insert in RAC environments;
- we cannot parallel insert into an IOT or a table with a bitmap index;
- we cannot use plan stability (outlines) for multi-table insert statements;
- we cannot use TABLE collection expressions; and
- we cannot use a sequence in the source query.
Note in addition to these the previous section on referential constraints and target table ordering.
further reading
For more information on multi-table insert statements, including syntax, read the SQL Reference. The RUNSTATS package used in this article can be downloaded here.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, October 2002
Back to Top