How many times have you heard someone say, “The rows always came back in the correct order, but since the *event* it’s now wrong. The *event* may be an upgrade or some maintenance task on the table etc.
Oracle clearly say,
ORDER BYclause to order rows returned by the statement. Without an
ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.”
This also applies for the
GROUP BY clause, that haunted people during their 10g upgrades.
GROUP BYclause groups rows but does not guarantee the order of the result set. To order the groupings, use the
Both these statements can be found in the docs for the SELECT statements.
So why not make the ORDER BY clause compulsory? The statement could include an explicit NOORDER, like this.
SELECT * FROM emp e ORDER BY e.empname SELECT * FROM emp e ORDER BY NOORDER
Of course, if that happened I would probably be in the group of people complaining about how ridiculous it is to force us to specify a clause we don’t need, but it would make people think… 🙂
Update: There seems to be a little confusion about what I am saying. I am not suggesting ordering should be mandatory. I am suggesting the ORDER BY clause could be mandatory, making you have to explicitly say if the output from the query is ordered or not. So these two statements would be functionally identical.
SELECT * FROM emp SELECT * FROM emp ORDER BY NOORDER