CODEWITHSUNDEEP
sqloracle-database
Z.Szymon
Z.Szymon

Reputation: 337

Will order by preserve?

create table source_table (id number);
insert into source_table values(3);
insert into source_table values(1); 
insert into source_table values(2);

create table target_table (id number, seq_val number);
create sequence example_sequence;

insert into target_table 
select id, example_sequence.nextval
    from 

> (select id from source_table ***order by id***);

Is it officially assured that for the id's with the lower values in source_table corresponding sequence's value will also be lower when inserting into the source_table? In other words, is it guaranteed that the sorting provided by order by clause will be preserved when inserting?

EDIT The question is not: 'Are rows ordered in a table as such?' but rather 'Can we rely on the order by clause used in the subquery when inserting?'.

To even more closely illustrate this, the contents of the target table in the above example, after running the query like select * from target_table order by id would be:

ID | SEQ_VAL 1 1 2 2 3 3

Moreover, if i specified descending ordering when inserting like this:

insert into target_table 
select id, example_sequence.nextval
    from 

> (select id from source_table ***order by id DESC***);

The output of the same query from above would be: ID | SEQ_VAL 1 3 2 2 3 1

Of that I'm sure, I have tested it multiple times. My question is 'Can I always rely on this ordering?'

Upvotes: 2

Views: 113

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50057

Tables in a relational database are not ordered, and any apparent ordering in the result set of a cursor which lacks an ORDER BY is an artifact of data storage, is not guaranteed, and later actions on the table may cause this apparent ordering to change. If you want the results of a cursor to be ordered in a particular manner you MUST use an ORDER BY.

Upvotes: 3

Related Questions