This is a quick note on the importance of ORDER BY for the order of the result set produced by a SELECT. The mantra is very simple:
Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
Repeat it as necessary many times everywhere, because it’s true (Update: but see the first comment from Sokrates for an exception to the rule). I want to say it again because yesterday I’ve seen it in action. A query that has been working correctly for several years without an issue has started to randomly return rows without an expected order. The query looks like this:
select ...
from (select ...
from t
where ...
order by col1
) t1
, t2
where t1.id = t2.id;
i.e. it joins an inline view with another table on the primary key column. It worked well before 11g due to the fact that t2 was always joined using nested loops. However, after an upgrade the CBO has decided to use nested loops join batching which causes the order of rows to change when the data is on disk. That’s it – the query starts to return “unordered” result set sometimes, which in fact doesn’t have to be ordered. I also recommend to read Doc 378254.1 Order of the resultset changes when using a different version/patchset.
3 Comments
Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
no rule without an exception.
Here is one:
select level from dual connect by level <= 2
It is guaranteed that this query always retrieves a 1 as first row and then a 2 as second row.
This is because it is a hierarchical query with exactly one root row and no sibling.
Hi Sokrates,
Yes, I know there is always an exception to a rule. Thank you for that.
As it usually is with the new functionality, sometimes it’s simpler to disable it. This “bug” was fixed by disabling new feature on the statement level with a hint
opt_param('_nlj_batching_enabled' 0)2 Trackbacks
[...] Timur posts a quick note on the importance of ORDER BY for the order of the result set produced by a SELECT. [...]
[...] Timur posts a quick note on the importance of ORDER BY for the order of the result set produced by a SELECT. [...]