One programmer is testing and comparing the same application which uses the same database structure, and the same data, only in two separate databases, one with Oracle 8 and one with Oracle 9.
The app runs a query with no ORDER BY clause.
He claims that the ORDER-BY-less query should return the rows in the same order in both databases.
I tell him there's no warranty of the same row order unless you explicity provide an ORDER BY clause.
The database has the same indexes and keys. But the explain plan shows that in one of the databases the engine is using the key of one of the joined tables whereas in the other database it's using another's.
He insinuates that the two DB environtments are not equal, which is so because they have different statistics, different rdbms engines, etc., but not because I failed to replicate every index the original database has.
I tell him he must explicity provide an ORDER BY clause if the order is really that important.
The question
So I can explain him better:
In what order does a query fetch rows when you don't explicity provide an ORDER BY clause, and why doesn't that query return the rows in the same order ?
Best Answer
From Wikipedia:
So it's undefined.
The SQL specification doesn't state the specific order that records are to be returned, so it's going to be implementation dependent.
With no indexes on the table, the sensible order would be the order in which the records were inserted. With a Primary Key defined, the sensible order would be the order of the Primary Key. But since the ANSI spec doesn't require a specific order, it's up to the vendor, and their sensibilities may differ from yours or mine.
Since the order is not stated in the specification, it is unwise to rely on the behavior of a particular vendor's implementation, since it can vary from one vendor to another, and the vendor may change the order any time they wish, without warning.
As you said, just include the
ORDER BYclause, if order is important.