52

In Oracle, what is the the default ordering of rows for a select query if no "order by" clause is specified.

Is it

  1. the order in which the rows were inserted
  2. there is no default ordering at all
  3. none of the above.
Old Pro
  • 24,624
  • 7
  • 58
  • 106
sengs
  • 6,647
  • 6
  • 30
  • 24
  • This question gets asked a lot (I've seen it pop up in several fora). I think it may be triggered by the false belief that "ORDER BY" will force the CBO to perform a sort, which is obviously more expensive than performing an unordered query. There are ways to improve sort performance in Oracle, but they don't require avoiding the "ORDER BY" clause. – Jeffrey Kemp May 26 '09 at 02:32
  • There are enough answers already to make the point, but just to add - without an explicit order the runtime engine will return the rows as it fetches them from storage. How it fetches rows depends on the execution plan. The execution plan can change, especially from 11g with the adaptive features such as statistics feedback. Rows are stored in blocks which are not guaranteed to be sequential. – William Robertson Jun 02 '19 at 16:06

8 Answers8

50

According to Tom Kyte: "Unless and until you add "order by" to a query, you cannot say ANYTHING about the order of the rows returned. Well, short of 'you cannot rely on the order of the rows being returned'."

See this question at asktom.com.

As for ROWNUM, it doesn't physically exist, so it can't be "freed". ROWNUM is assigned after a record is retrieved from a table, which is why "WHERE ROWNUM = 5" will always fail to select any records.

@ammoQ: you might want to read this AskTom article on GROUP BY ordering. In short:

Does a Group By clause in an Query gaurantee that the output data will be sorted on the Group By columns in order, even if there is NO Order By clause?

and we said...

ABSOLUTELY NOT,

It never has, it never did, it never will.

DCookie
  • 42,630
  • 11
  • 83
  • 92
  • Thanks, I know that article. 2 years ago, I studied it with great care, after literaly spending weeks with the question: Why does this program behave differently on 10g? – Erich Kitzmueller May 23 '09 at 04:52
  • Simply put, because Oracle is doing something differently in 10g. It's not a bug in Oracle, however! Oracle NEVER siad GROUP BY would sort the results, and Tom shows how it can fail in 9i. Just because it often seemed that way meant people relied on it. That article, in addition to being informative, is one of the most amazing illustrations of people being unable or unwilling to accept a simple concept, because it's different than their own anecdotal observations. It's also pretty funny. IF YOU WANT YOUR DATA SORTED, USE ORDER BY. – DCookie May 23 '09 at 14:08
  • @DCookie is this behavior is limited to Oracle or it is applicable to all relational database like MySQL,Postgres? – Narendra Jaggi Apr 09 '16 at 12:45
  • @NarendraJaggi: this is true for all relational databases. –  Jul 20 '16 at 20:34
23

There is no explicit default ordering. For obvious reasons, if you create a new table, insert a few rows and do a "select *" without a "where" clause, it will (very likely) return the rows in the order they were inserted.

But you should never ever rely on a default order happening. If you need a specific order, use an "order by" clause. For example, in Oracle versions up to 9i, doing a "group by" also caused the rows to be sorted by the group expression(*). In 10g, this behaviour does no longer exist! Upgrading Oracle installations has caused me some work because of this.

(*) disclaimer: while this is the behaviour I observed, it was never guaranteed

Erich Kitzmueller
  • 36,381
  • 5
  • 80
  • 102
  • 4
    Yes to the answer, but to nitpick: Not so "obvious", actually - there's no guarantee new rows will be inserted into a new table in any particular order, and there's no guarantee a SELECT without an ORDER BY will necessarily visit all the blocks in the table in the same order they were created. – Jeffrey Kemp May 25 '09 at 02:09
  • 2
    Yes, there is no guarantee per se. But I would be hard-pressed to create a scenario where it doesn't work like that. – Erich Kitzmueller May 25 '09 at 04:38
  • 4
    If you create a table with 100 records, delete the first 50 and then insert another 10, Oracle will tend to re-use the space from the deleted rows (subject to lots of ifs, buts etc). So even if Oracle visits the blocks in the same order, the last 10 records inserted will tend to come first in a query without an ORDER BY – Sten Vesterli May 26 '09 at 20:05
  • 1
    Clearly. That is why his example was `if you create a **new** table, insert a few rows and do a select` Although I would have said `...it will _most likely_ return...` – Amit Naidu Apr 16 '13 at 09:36
6

It has already been said that Oracle is allowed to give you the rows in any order it wants, when you don't specify an ORDER BY clause. Speculating what the order will be when you don't specify the ORDER BY clause is pointless. And relying on it in your code, is a "career limiting move".

A simple example:

SQL> create table t as select level id from dual connect by level <= 10
  2  /

Tabel is aangemaakt.

SQL> select id from t
  2  /

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rijen zijn geselecteerd.

SQL> delete t where id = 6
  2  /

1 rij is verwijderd.

SQL> insert into t values (6)
  2  /

1 rij is aangemaakt.

SQL> select id from t
  2  /

        ID
----------
         1
         2
         3
         4
         5
         7
         8
         9
        10
         6

10 rijen zijn geselecteerd.

And this is only after a simple delete+insert. And there are numerous other situations thinkable. Parallel execution, partitions, index organised tables to name just a few.

Bottom line, as already very well said by ammoQ: if you need the rows sorted, use an ORDER BY clause.

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55
4

You absolutely, positively cannot rely on any ordering unless you specify order by. For Oracle in particular, I've actually seen the exact same query (without joins), run twice within a few seconds of each other, on a table that didn't change in the interim, return a wildly different order. This seems to be more likely when the result set is large.

The parallel execution mentioned by Rob van Wijk probably explains this. See also Oracle's Using Parallel Execution doc.

Kelvin
  • 20,119
  • 3
  • 60
  • 68
-2

It is impacted by index , if there is index ,it will return a ascending order , if there is not any index ,it will return the order inserted .

Amir Buzo
  • 69
  • 9
-2

You can modify the order in which data is stored into the table by INSERT with the ORGANIZATION clause of the CREATE TABLE statement

d0dulk0
  • 112
  • 2
  • 8
  • Umm not really. ORGANIZATION does nothing for heap tables (which is the default). It only applies for `organization indexed` and `external` tables, which have particular usages. [Find out more](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6) – APC Jun 01 '19 at 16:09
-3

Although, it should be rownnum (your #2), it really isn't guaranteed and you shouldn't trust it 100%.

Ricardo Villamil
  • 5,031
  • 2
  • 30
  • 26
-7

I believe it uses Oracle's hidden Rownum attribute.

So your #1 is probably right assuming there were no deletes done that might have freed rownums for later use.

EDIT: As others have said, you really shouldn't rely on this, ever. Besides deletes theres a lot of different conditions that can affect the default sorting behavior.

Zenshai
  • 10,307
  • 2
  • 19
  • 18
  • 1
    ROWNUM isn't an attribute, but assigned only when queried. ROWID isn't an attribute either - it simply expresses the file, block and row as a single value. It does get stored on indexes as the pointer to the real row. – Gary Myers May 22 '09 at 22:13