4

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.

Florian Müller
  • 7,448
  • 25
  • 78
  • 120

2 Answers2

14

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!

  • It can change? I actually never saw a change on the first few records, they are always the same..? – Florian Müller Jun 12 '13 at 06:44
  • 7
    @FlorianMüller - every day, on my drive to work, I see a blue car, followed by a red car, followed by a green car. Does that, for some reason, entitle me to expect that this must *always* happen? Or is it just a coincidence and not something I should rely on always being true? – Damien_The_Unbeliever Jun 12 '13 at 06:52
  • @FlorianMüller: see my SQLFiddle example. –  Jun 12 '13 at 06:53
  • @Damien_The_Unbeliever: If you *really* and *every day* see a blue car followed by a red car followed by a green car then I actually don't think this is a coincidence. Must it *always* happen? Who knows? Until you know *why* you observe this odd pattern of colors on your drive to work you cannot say with certainity that it must change. – René Nyffenegger Jun 12 '13 at 07:22
  • @RenéNyffenegger unless you know why you observe the pattern of colours then you have no basis to believe anything. – David Aldridge Jun 12 '13 at 08:00
  • Of course typically what you'd see is the rows returned in the order by which the database identified them as part of the result set, with the caveat that in parallel query they have to be sent from query slaves to the query coordinator in batches according to the row and message size. So you might observe ordering, particularly if the table was loaded with a direct path insert from an ordered query, but you can never rely on that. Excellent demo, btw. – David Aldridge Jun 12 '13 at 08:03
  • Oh also a GROUP BY is sometimes said to perform an ORDER BY, but that is dependent on the GROUP BY algorithm used, which can not be relied on to stay constant, and does not respect linguistic sort. – David Aldridge Jun 12 '13 at 08:04
  • @DavidAldridge: this is exactly why the example with the car-colors does not explain why Florian observed the same order with his query. With SQL/Oracle, we know why we observe this pattern, and why we can't rely on it being repeated. – René Nyffenegger Jun 12 '13 at 08:06
  • 2
    @RenéNyffenegger are you making a point about the unreliability of car colour ordering, or Oracle row ordering? :) – David Aldridge Jun 12 '13 at 08:09
  • @FlorianMüller: the order of the first few rows can change when you update them. Unless you've been merge joining or hash joining sets, the default order basically is the order in which the physical rows are read -- and the latter is susceptible to change when rows are updated. – Denis de Bernardy Jun 12 '13 at 08:11
  • @DavidAldridge: I was trying to point out that the cars don't prove that Oracle doesn't change the ordering. – René Nyffenegger Jun 12 '13 at 08:14
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.

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284