Default row ordering for select query in oracle

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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 .