ORDER BY

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.

About these ads

3 Comments

  1. Posted April 6, 2011 at 18:25 | Permalink | Reply

    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.

    • Posted April 6, 2011 at 19:06 | Permalink | Reply

      Hi Sokrates,

      Yes, I know there is always an exception to a rule. Thank you for that.

  2. Posted April 13, 2011 at 13:04 | Permalink | Reply

    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

  1. [...] Timur posts a quick note on the importance of ORDER BY for the order of the result set produced by a SELECT. [...]

  2. [...] Timur posts a quick note on the importance of ORDER BY for the order of the result set produced by a SELECT. [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 287 other followers