Multi table insert

 

multi-table inserts in oracle

 

 

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.