Multi-Table Insert in Oracle

It is important to understand the fundamentals of the tool before using it, especially in production. We learnt this, costly lesson through struggle. In this BLOG, I’ll document my experience, so it may help others.

Couple of years back, one of the developer learnt about multi-table insert ability in Oracle. We were running Oracle version 10g and in our application many primary/foriegn keys were generated using sequences.

The new code worked fine. Performance improvement (using multitable insert) in batch programs, instead of multiple insert statement, were GREAT. Significant (but not effective testing) was performed. Everything passed and code went to production.
However, after the code was in production for over 1 year, the batch programs (many of them) occassionally resulted in ORA-02291: integrity constraint (nnnnnnnn) violated – parent key not.

And then the frequencey of failure increased to almost multiple time a day. The processes run many times a day.

Since the error was not “reproducible” consistently in QA or development environment, the struggle continued. Developers blaming DBA and DBA blaming developers; Every one blaming Everyonelse. Furstration grew.

Now, a quick search for Note 265826.1 (in google, or MOS) might have resolved everything, however no one even thought of looking under the cover or search for this unknown MOS note.
Let us take a look at some sample code:
For evaluating the behavior of Multi table insert using Oracle sequence as keys, with Parent-child (Master-Detail) relationships between tables, we’ll try a simple example. Here is the setup code.

01 drop table tb;
02 drop table ta;
04 create table ta (a number not null primary key ,
05 da varchar2(30) not null);
07 create table tb (b number not null references ta(a) ,
08 db varchar2(30) not null);
10 drop sequence ts;
11 create sequence ts;

When following SQL is executed, everything works fine.

1 insert all
2 into ta (a, da) values (ts.nextval, xx)
3 into tb (b, db) values (ts.currval, xx)
4 select 'XXXXX' xx from dual connect by level < 500;

However, when you increase the the number of records to be inserted as shown below, we ran into ORA-02291.
For eg.

1 insert all
2 into ta (a, da) values (ts.nextval, xx)
3 into tb (b, db) values (ts.currval, xx)
4 SELECT 'XXXXX' xx from dual connect by level < 500000;

This is documented BUG of Multi-Table Insert: Note 265826.1. This was intitated in 2004. The workaround is described however the BUG is not corrected.
Developer tried adding ORDER BY clause and spent four days, struggling to do various alternatives.
Hopefully this will be addressed (if addressable) in some release. It still exists in release.

MOS note excerpt:
This issue is an open bug:
Based on the above:
“The order of the tables into which Oracle inserts data is not determinate. Therefore, before
issuing a multitable insert statement, you should defer any constraints and disable any triggers
that depend on a particular table order for the multitable insert operation.”


1. Disable the foreign key when run such MultiPath Inserts.
2. Use DEFERRED CONSTRAINTS so the checkout happens only at Commit time.
About Deferred Constraints check Metalink “Deferred Constraints Example”

MOS document: Note 265826.1

Hope this helps others.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s