Oracle SQL returns rows in arbitrary fashion when no "order by" clause is used

Question

Maybe someone can explain this to me, but when querying a data table from Oracle, where multiple records exist for a key (say a customer ID), the record that appears first for that customer can vary if there is no implicit "order by" statement enforcing the order by say an alternate field such as a transaction type. So running the same query on the same table could yield a different record ordering than from 10 minutes ago.

E.g., one run could yield:

Cust_ID, Transaction_Type
123 A
123 B

Unless an "order by Transaction_Type" clause is used, Oracle could arbitrarily return the following result the next time the query is run:

Cust_ID, Transaction_Type
123 B
123 A

I guess I was under the impression that there was a database default ordering of rows in Oracle which (perhaps) reflected the physical ordering on the disk medium. In other words, an arbitrary order that is immutable and would guarantee the same result when a query is rerun.

Does this have to do with the optimizer and how it decides where to most efficiently retrieve the data?

Of course the best practice from a programming perspective is to force whatever ordering is required, I was just a little unsettled by this behavior.

Answer 1

The order of rows returned to the application from a SELECT statement is COMPLETELY ARBITRARY unless otherwise specified. If you want, need, or expect rows to return in a certain order, it is the user's responsibility to specify such an order.

(Caveat: Some versions of Oracle would implicitly sort data in ascending order if certain operations were used, such as DISTINCT, UNION, MINUS, INTERSECT, or GROUP BY. However, as Oracle has implemented hash sorting, the nature of the sort of the data can vary, and lots of SQL relying on that feature broke.)

Answer 2

There is no default ordering, ever. If you don't specify ORDER BY, you can get the same result the first 10000 times, then it can change.

Note that this is also true even with ORDER BY for equal values. For example:

Col1 Col2
1    1
2    1
3    2
4    2

If you use ORDER BY Col2, you still don't know if row 1 or 2 will come first.

Answer 3

Just image the rows in a table like balls in a basket. Do the balls have an order?

I dont't think there is any DBMS that guarantees an order if ORDER BY is not specified.

Some might always return the rows in the order they were inserted, but that is an implementation side effect.

Some execution plans might cause the result set to be ordered even without an ORDER BY, but again this is an implementation side-effect that you should not rely on.