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 pretends 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 clase.
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 or 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.
So I can explain him better:
In what order does a query fetch rows when you don't explicity write an ORDER BY clause, and why doesn't that query return the rows in the same order ?