1 2 Previous Next 24 Replies Latest reply on Oct 14, 2018 1:23 AM by Peter Gjelstrup

    insert all order

    kaericn

      Is the insert all guarantee the insert orders e.g.

       

      We have the scenario similar to the code sample below that we can parent key not found error.

      And we suspect its because the order of insertion is not guaranteed in the sql ?

       

      Is there a way that we can guarantee the order of operation in DML  as such ?

       

      create table tt_p as select * from user_objects where 1 = 0
      
      
      create table tt_c as select * from user_objects where 1 = 0
      

       

       

      ALTER TABLE 
         tt_p 
      ADD CONSTRAINT 
         pk_object_id
      PRIMARY KEY (object_id);
      
      
      ALTER TABLE tt_c
      ADD CONSTRAINT object_id_fk1
      FOREIGN KEY (object_id)
      REFERENCES tt_p (object_id);
      
      
      insert all into  tt_p
      into tt_c
      select * from user_objects;
      
        • 1. Re: insert all order
          John Thorton

          kamoneric wrote:

           

          Is the insert all guarantee the insert orders e.g.

           

          We have the scenario similar to the code sample below that we can parent key not found error.

          And we suspect its because the order of insertion is not guaranteed in the sql ?

           

          Is there a way that we can guarantee the order of operation in DML as such ?

           

          Utter & complete NONSENSE!

           

          Rows in a table have NO inherent order.

           

          Your imagination greatly exceeds your technical knowledge by at least two orders of magnitude.

           

          How can we reproduce what you report parent key not found error?

          • 2. Re: insert all order
            mathguy

            I copied and pasted your code exactly as you posted it, and the INSERT went through without any problems. I did not get your error.

             

            I ran it in Oracle 12.2.0.1; what is your Oracle version? (Please remember to always include it in all your posts, even if you don't think it is relevant. Most of the time it is.) It is possible that the INSERT ALL works differently in different versions.

            • 3. Re: insert all order
              mathguy

              I thought I had read about this, and I found it. Toon Koopelaars has written about it: RuleGen: Statement-level constraint validation: MERGE + MTI

              (To find the relevant section quickly, on that web page search for the phrase "But wait a minute".)

               

              One would wish that constraints are only checked after all the inserts into all tables are completed. (This is the STATEMENT level validation Toon is talking about: a constraint should be considered to be "violated" only if it is violated after ALL the inserts in the statement are completed.) Alas, as he describes in his simple examples, that is simply not true (or at least it wasn't in earlier versions - I will test his examples on my machine when I get a chance, to see if the problem has been corrected).

               

              In any case, I don't think you are "seeing things"; you have run into an issue that is not new.

              • 4. Re: insert all order

                We have the scenario similar to the code sample below that we can parent key not found error.

                Provide an example that causes the exception you report

                 

                SHOW US:

                 

                1. WHAT you do

                2. HOW you do it

                3. WHAT results you get

                And we suspect its because the order of insertion is not guaranteed in the sql ?

                Haven't tested DML error logging with multitable operations.

                 

                Try testing it and see if it is supported. If so it will show you which rows are causing problems.

                • 5. Re: insert all order
                  mathguy

                  It is clear WHAT is causing the issue. It is not clear WHY. There should be no issue - it seems like a bug (as Toon explains in his article - see a link in Reply #3).

                  • 6. Re: insert all order
                    John Thorton

                    kamoneric wrote:

                     

                    1. insertallintott_p
                    2. intott_c
                    3. select*fromuser_objects;

                     

                    What SQL above does is INSERT new rows into both Parent & Child table "concurrently".

                    It provides nothing to ensure that requisite Parent row will exist before Child row is INSERTED.

                    The fact that error is thrown should not be a surprise or mystery to anyone.

                    Please post the required ORDER BY clause that would provide 100% assurance that no Parent Not Found error never occurs.

                     

                    The simple solution is to add the CONSTRAINT after the data has been loaded.

                    • 7. Re: insert all order
                      Peter Gjelstrup

                      kamoneric wrote:

                      Is the insert all guarantee the insert orders e.g.

                       

                      For what it is worth, I think not - At least this one of the things having irritated me most often.

                      Whenever I am doing multi table operations like Insert All, I run into that Parent Key does not exist.

                      Or rather, I find it to work as expected when source select return only "few" rows. With more than a houndred or thousand, it starts failing sporadically.

                       

                      I have never worked seriously with versions higher than 11.2.0.3, things could ofc have changed since then.

                       

                      My current standing is that I do not use Multi table inserts, while at the same time having FKs enabled.

                       

                      P.S: One "trick" can be to have FKs as deferrable on tables where you wish do multi table operations.

                       

                      BR

                      Peter

                      • 8. Re: insert all order
                        mathguy

                        You seem totally ignorant of the concept of "set processing", or specifically of "statement level constraint validation." Perhaps you should not share your opinions on other people's knowledge level.

                        • 9. Re: insert all order
                          mathguy

                          It is actually very easy to reproduce the problem - here is an example. Clearly the relational integrity constraint is checked after the first insert, instead of it being checked only at the end of the statement.

                           

                          It seems as if Oracle's implementation of INSERT ALL is to execute two separate INSERT statements (or at least it is checking the constraint as if that was the case). Tested on 12.2.0.1, so it hasn't changed.

                           

                          create table a ( id number primary key );

                          create table b ( id number references a );

                           

                          insert all

                            into b

                            into a

                              select 0 from dual;

                            

                          Error starting at line : 4 in command -

                          insert all

                            into b

                            into a

                              select 0 from dual

                          Error report -

                          ORA-02291: integrity constraint (MATHGUY.SYS_C0012762) violated - parent key not found

                          • 10. Re: insert all order
                            kaericn

                            It is a sample.

                            My question is it a way to gurantee the order of insertion.

                            If it's not allowed for temporary fk violation?

                             

                            We are splitting the source to parent and child

                            The source is some extraction sql

                             

                            From what we observe, the order is not guaranteed.

                            And temp violation of fk is not allowed...

                             

                            And we log error with complaining

                            Parent key not find in our DML errlog

                             

                            It is a bug as we suppose the child

                            And parents are from the same source

                            And it should allow temp violation of fk

                            Or gurantee the insertion order.

                             

                            My manager says convert into bulk collect +

                            Insert.all SQL +

                            Forall + a cursor will solve the problem.

                             

                            I did what he asks but l am not sure it really

                            Fix the issue ??

                             

                            We are only testing my.manager solution on less than 0.1 pct

                            Of the data and it's not a very convincing solution to me..

                            • 11. Re: insert all order
                              kaericn

                              I am not clear on what do you mean

                              using "order.by"

                               

                              Can you demo with a sample ??

                              • 12. Re: insert all order
                                kaericn

                                I am not sure if my manager and team lead

                                Will agree on changing the fk def...

                                • 13. Re: insert all order
                                  kaericn

                                  We are on 12c.

                                  We observe the same behavior like you see on 11g.

                                  • 14. Re: insert all order
                                    mathguy

                                    Alas, in some cases deferring constraint validation is unavoidable. If INSERT ALL didn't have this problem, it would be the right workaround (see example below), but since it doesn't work as it should, I am not aware of a general solution other than deferring the constraint until after the transaction.

                                     

                                    Example: you must populate two tables, People and Countries. People has a column COUNTRY, fk pointing to the Countries table, and the Countries table has a column RICHEST_PERSON, fk pointing back to people. Now assume you start with a very simple dataset: there is only one country, and only one person. There is NO order of inserting into the two tables that does NOT violate one or the other of the fk constraints after the FIRST insert. The constraint MUST be checked only after all the inserts have completed.

                                     

                                    If you were inserting into a single table, the constraint would be checked after all the rows are inserted (regardless of the order in which they are generated by the row source). Alas, with multiple tables, INSERT ALL does not work that way (even though it should).

                                    1 2 Previous Next