SQL & PL/SQL

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