Default row ordering for select query in oracle

Solution 1:

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.

Solution 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.