Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it safe to use Oracle's multi-table insert statement to insert into a (foreign key constrained) parent and child table?

With minimal examples, I've found that it works as long as the parent table comes before the child table in the into list. Can I rely on this or should I make the constraint deferrable?

share|improve this question
    
Are you talking about INSERT ALL? –  Phil Aug 29 '12 at 19:48
    
@Phil: Yes I am. –  Isaac Kleinman Aug 29 '12 at 19:50
    
What about two inserts? Why go the roundabout way with deferrable constraints when the simple method of multiple inserts has always worked? –  Vincent Malgrat Aug 30 '12 at 8:59
    
@Vincent sometimes it takes a fraction of the time to insert all - that may be Isaac's reason? –  Jack Douglas Aug 30 '12 at 14:06
    
@JackDouglas Performance is a good reason, but INSERT ALL has lots of limitations: no parallel execution, no insert in a view. This leads me to think that this statement is less optimized that standard INSERT statements, especially when inserting in multiple tables. –  Vincent Malgrat Aug 30 '12 at 14:24

2 Answers 2

No, you can't depend on this. SQL is declarative, not procedural, so within a statement you can't guarantee the order of execution. Since the entire INSERT ALL statement is considered a single statement (doc), you can't guarantee that one INSERT will be before another.

By definition an INSERT FIRST must execute the first INTO passing the evaluated conditions. We might expect INSERT ALL to behave similarly. This appears to be the case:

DROP TABLE T1;
CREATE TABLE T1 AS (SELECT 'a' c1, 0 c2, 0 c3 FROM dual WHERE 1=2);
INSERT ALL 
   WHEN mod(x,2)<>0 THEN INTO T1 VALUES ('a', x, mod(x,2)) 
   WHEN mod(x,2)=0 THEN INTO T1 VALUES ('b', x, mod(x,2)) 
   SELECT Level x FROM dual CONNECT BY Level <=20;
COMMIT;   
SELECT rowid, c1, c2, c3 FROM t1;

However, even though we can demonstrate a particular behavior on a particular platform/version/patchset still doesn't make this a guarantee.

Oracle-developer.net says it explicitly:

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.

share|improve this answer
    
Oracle do actually consider this non-deterministic behaviour a bug (see My Oracle Support article 265826.1 for bug 2891576). However, given this was raised back in 2003 for version 9.2.0.3, I doubt there's much chance of this being fixed anytime soon. –  Chris Saxon Aug 30 '12 at 16:04
    
@Chris Saxon Excellent find. The report demonstrates a case in which modifying the size of an unrelated column affects which into is done first. As expected the workaround is to use DEFERRED CONSTRAINTS. –  Leigh Riffel Aug 30 '12 at 17:42

Isaac,

You can not rely on this as oracle does not guarantee the order of INSERT. The correct way to do this is, as you mentioned, with the deferred foreign key constraint.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.