In what order are rows fetched when no ORDER BY clause is provided?
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.
The question
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 ?
No comments:
Post a Comment