10 Replies Latest reply on Jun 8, 2010 6:04 PM by Sudhakar_B

    insert into .. select from table order by

    Ward Flores-Oracle
      Hello all,

      Does Oracle guarantee that the data will be inserted in order when using "insert into .. select from table order by"? It seems to be the case from the following test I've done but I just wanted to double check.

      Noticed that I'm interested in the order of the data being inserted and not_ in the resulting physical order in the database.

      Regards,
      wf


      SQL> create table test_insert_in_order (n number);

      Table created.

      SQL> insert into test_insert_in_order (n)
      2 select column_value from table (my_integer_tbl_t(3,2,1,4));

      4 rows created.

      SQL> select rowid, n from test_insert_in_order;

      ROWID N
      ------------------ ----------
      AAAq6fAAFAACj6zAAM 3
      AAAq6fAAFAACj6zAAN 2
      AAAq6fAAFAACj6zAAO 1
      AAAq6fAAFAACj6zAAP 4

      SQL> delete from test_insert_in_order;

      4 rows deleted.

      SQL> insert into test_insert_in_order (n)
      2 select column_value from table (my_integer_tbl_t(3,2,1,4))
      3 order by column_value;

      4 rows created.

      SQL> select rowid, n from test_insert_in_order;

      ROWID N
      ------------------ ----------
      AAAq6fAAFAACj6zAAQ 1
      AAAq6fAAFAACj6zAAR 2
      AAAq6fAAFAACj6zAAS 3
      AAAq6fAAFAACj6zAAT 4
        • 1. Re: insert into .. select from table order by
          Sudhakar_B
          All that Oracle will guarantee is that *data returned from a select that has order by clause will be in the specified order* .
          Having said that:
          You said...
          +
          Noticed that I'm interested in the order of the data being inserted and 
          not in the resulting physical order in the database.
          +
          I am curious as to what is the goal or reason for you interest.
          If your observation is based on the ROWID values, then (IMHO) it is not correct to come to the conclusion based on ROWID.

          As for INSERTED data.. General Oracle tables are "heap" and that is what should be expected from them.
          If I have misunderstood the point you are making, please elaborate.
          vr,
          Sudhakar B.
          • 2. Re: insert into .. select from table order by
            ajallen
            It does not matter the order you insert rows into a table. Oracle does not guarantee that they will be returned in that order. Oracle, being a relational database, does not have to guarantee the order of rows returned unless you specify ORDER BY on the select statement.

            The foregoing being true for the basic (default) heap table. However, if you want rows stored in key sequence, specify ORGANIZATION INDEX on the table create statement. That will make your table balanced tree and your rows will be stored in PK sequence - if that is of any help.

            Please, why do you care about the order of rows returned.
            • 3. Re: insert into .. select from table order by
              Toon Koppelaars
              wflores wrote:
              Hello all,

              Does Oracle guarantee that the data will be inserted in order when using "insert into .. select from table order by"? It seems to be the case from the following test I've done but I just wanted to double check.
              As far as I know: yes. But they can be stored scattered throughout the table segment. This all depends on the current state of that segment: most specifically the current status of the freelists for that segment.

              >
              Noticed that I'm interested in the order of the data being inserted and not_ in the resulting physical order in the database.
              We are all very curious why you are interested in this. The only reason I can think of, is that you have a row-trigger on that table, and you want to make sure that whatever logic that trigger holds, is fired in order of "column_value".
              • 4. Re: insert into .. select from table order by
                Ward Flores-Oracle
                Hello,

                The reason why I'm interested in the ordering of the inserts is because we are facing a deadlock with our bulk insert query. If my assumptions are correct, concurrent bulk inserts can result on a deadlock if the data being inserted is not previously ordered.


                SQL> create table deadlock_on_inserts
                2 (n number primary key);

                Table created.

                SQL> commit;

                Commit complete.

                TRANSACTION 1 ```````````````````````````` TRANSACTION 2
                =========== ``````````````````````````` ===========

                SQL> insert into deadlock_on_inserts values (1); ``` SQL> insert into deadlock_on_inserts values (2);

                1 row created. `````````````````````````````` 1 row created.


                SQL> insert into deadlock_on_inserts values (2); ``` SQL> insert into deadlock_on_inserts values (1);
                insert into deadlock_on_inserts values (2)
                *
                ERROR at line 1:
                ORA-00060: deadlock detected while waiting
                for resource


                Now, since we cannot lock the rows because they don't exist yet, I thought a solution would be to simply order the values before performing the insert (I prefer to deal with the a unique constraint exception). Is this a proper solution?

                Regards,
                wf
                • 5. Re: insert into .. select from table order by
                  If my assumptions are correct, concurrent bulk inserts can result on a deadlock if the data being inserted is not previously ordered.

                  Nice fairy tale. Did you made that one up or someone else?

                  Is this a proper solution?
                  Ordering data will have zero impact on how Oracle deals with locks.

                  --------
                  Sybrand Bakker
                  Senior Oracle DBA
                  • 6. Re: insert into .. select from table order by
                    Ward Flores-Oracle
                    sybrand_b wrote:
                    If my assumptions are correct, concurrent bulk inserts can result on a deadlock if the data being inserted is not previously ordered.

                    Nice fairy tale. Did you made that one up or someone else?
                    Actually, I tested as illustrated above.
                    • 7. Re: insert into .. select from table order by
                      Sudhakar_B
                      Agree with some caution (May be lots of caution).
                      In such situation two or more sessions processing an ordered result set will reduce the probability of dead lock.
                      This has less to do with the order in which records are inserted into target table, in the sense that any kind of processing that will be require locking could potentially result in deadlock.

                      Having said that,
                      Why are two or more large "bulk operations" processing same result set?
                      Are you attempting "parallel processing"?
                      If yes: This is not a good approach. Transaction in one session will wait on the transaction from another session.
                      If this large upload process you could use Oracle parallel processing in a single session and let oracle do the parallel process.
                      If you have control of the sessions that are processing this data, may be you can "logically slice" the data such that diffeerent sessions process mutually exclusive set of data.

                      Please describe your goal and why you are taking this approach for large bulk operations,, may be forum members can provide additional help and suggestions.
                      vr,
                      Sudhakar B.
                      • 8. Re: insert into .. select from table order by
                        What your post illustrated is you deliberately created a dead lock.
                        How rare are deadlocks? As Oracle trace files state: dead locks indicate a bug in the code.

                        -------
                        Sybrand Bakker
                        Senior Oracle DBA
                        • 9. Re: insert into .. select from table order by
                          Ward Flores-Oracle
                          Sudhakar_B wrote:
                          Agree with some caution (May be lots of caution).
                          In such situation two or more sessions processing an ordered result set will reduce the probability of dead lock.
                          Sorry, but I don't see why processing an ordered result set will only_ reduce the probability of deadlock.
                          This has less to do with the order in which records are inserted into target table, in the sense that any kind of processing that will be require locking could potentially result in deadlock.

                          Having said that,
                          Why are two or more large "bulk operations" processing same result set?
                          In our current design, we found out that in some very rare cases we can end up with two or more entries having the same primary key. Therefore, if we get very unlucky, we can face the scenario described in my post above which will result on a deadlock. The ideal solution is, of course, a better choice of the primary key. However, in the meantime, I thought we could avoid deadlocks ordering the result set.
                          Are you attempting "parallel processing"?
                          Yes, kind of. I mean not at the db layer.
                          If yes: This is not a good approach. Transaction in one session will wait on the transaction from another session.
                          We make sure to push data in chunks such as that the waiting time is not significant.
                          If this large upload process you could use Oracle parallel processing in a single session and let oracle do the parallel process.
                          If you have control of the sessions that are processing this data, may be you can "logically slice" the data such that diffeerent sessions process mutually exclusive set of data.
                          I'm not very familiar with Oracle parallel processing, but at a first glance I think I would end up with the same problem if the data is not ordered.
                          Please describe your goal and why you are taking this approach for large bulk operations,, may be forum members can provide additional help and suggestions.
                          vr,
                          Sudhakar B.
                          • 10. Re: insert into .. select from table order by
                            Sudhakar_B
                            @OP,
                            If exactly same processing on exactly same ordered result set, is done in two or more sessions, then deadlock will not occur.
                            That is why I was asking about "parallelization: exact same code is executed in multiple sessions".

                            +
                            In our current design, we found out that in some very rare cases we can end up with two or more 
                            entries having the same primary key. Therefore, if we get very unlucky, we can face the scenario described in 
                            my post above which will result on a deadlock. The ideal solution is, of course, a better choice of the primary key. 
                            However, in the meantime, I thought we could avoid deadlocks ordering the result set.
                            +
                            Agreed. As you said this is rare scenario, ordering the same incoming result set for same sequence of processing will avoid deadlock.

                            Oracle Documentation (below) talks about multi-table deadlock avaoidance, but same concept applies to single table multi-row deadlock as well.
                            http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/consist.htm

                            vr,
                            Sudhakar B.