What is the default SQL result sort order with 'select *'? [duplicate]

Question

If I perform a select * from mytable, in which order will the records get displayed? Will it take the first column or order it by some sort of meta data?

I am using an Oracle Database.

Answer 1

There is NO default "sort" order. Rows in a relational table are not sorted.

The only (really: the only) way to get a specific order is to use an ORDER BY

Whatever order you see when running a SELECT without ORDER BY is pure coincident and can change with the next execution.

The order can change because of various reasons:

• other sessions are running the same statement
• the table was updated
• the execution plan changes
• ...

Here is a little SQLFiddle that shows you how the "order" can change: http://sqlfiddle.com/#!4/19d14/2

Note that the initial "order" is not even the same as the insertion order!

Answer 2

Typically, there is NO sort order unless specified.

This can vary from storing, indexing, index storing, index selection, execution plan, database engine implementation, willy nilly like I feel.

So as you can see, you either specify a sort order, or you dont count on it.