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:
Setup:
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;

03

04	create table ta (a number not null primary key ,

05	da varchar2(30) not null);

06

07	create table tb (b number not null references ta(a) ,

08	db varchar2(30) not null);

09

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 11.2.0.2 release.

MOS note excerpt:
This issue is an open bug:
MULTI TABLE INSERT (INSERT ALL) FAILS WITH ORA-2291
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."

FIX

WORKAROUND:
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"

References:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6915127515933
MOS document: Note 265826.1

Hope this helps others.