Moving `order by` Significantly Improves Execution Time

oracleorder-by

I provide a simply query that, I think, resembles my real query.

I'm using Oracle.

Given:

create table main_table(a NUMBER, b VARCHAR2(10))
create table rates_table(a NUMBER, rate NUMBER)
create table other_table(a NUMBER, c VARCHAR2(10))

and their data:

select * from main_table

1   foo
2   bar

select * from other_table

2 bar

select * from rates_table

2 42.2

The following query finds all a's in main_table where its b matches other_table's c, limiting to the first 1000 results. Then, it left joins rates_table, getting the rate from other_table:

select o.a, o.b, rt.rate from (
  select mt.a, mt.b from main_table mt
  left join other_table ot on mt.a = ot.a
  where mt.b = ot.c
  and rownum < 1000
) o
left join rates_table rt on rt.a = o.a
order by o.a, o.b

Output:

2 bar 42.2

In my real query, which, again, resembles the above query, I noticed a significant (11 seconds to < 1 second) execution time when moving the order by to within the inner SELECT:

select o.a, o.b, rt.rate from (
  select mt.a, mt.b from main_table mt
  left join other_table ot on mt.a = ot.a
  where mt.b = ot.c
  and rownum < 1000
  order by mt.a, mt.b -- <---
) o
left join rates_table rt on rt.a = o.a

It, too, returns:

2 bar 42.2

Here's the first query's EXPLAIN PLAN:

enter image description here

And the second's:

enter image description here

In general, does the placement of order by matter, i.e. will the results differ depending on where I put this clause? I'm not asking about this particular query, since the data only has a few rows.

Why would the placement of order by, i.e. in my real query, result in such an improvement – 11 seconds to < 1 second?

Best Answer

Comparing apples to oranges. The 2 queries and the results they return are not the same.

First query retrieves 999 rows from main_table + other_table, joins to rates_table and sorts the result. The result of the 3 table join can be 999 rows or 10 million rows as well, depending on the data. If it is 10 million, it has to sort 10 million rows.

Second query retrieves 999 rows from main_table + other_table and sorts them right after. Finally joins the rates_table, and does not sort the final result.

The first query sorts everything, the seconds query sorts only the result of the subquery. Sorting 10 million rows will most likely need more resources+time than sorting 999 rows. The cost of sorting is often overlooked.

The first query returns a sorted resultset, but for the second query, there is no guarantee of returning a sorted result.

The second query may return inconsistent results between different database versions because of how the optimizer works in different versions. For example if column a is indexed, the optimizer may choose to access the table using the index, and returns the first 999 rows sorted anyway, because data is already sorted in the index. Or it may choose to scan the table, get whatever 999 rows it can, then sort that. This can be easily tested with a hints such as: /*+ optimizer_features_enable('12.1.0.2')*/ and providing different versions.

The first query is guaranteed to return the same result on all versions, as long as the data is organized the same way. As there is no criteria specified for getting the first 999 rows (first, based on what?), after reorganizing the table (move, shrink, export/import, redefinition), it may also return different results for the same base data. In this aspect, both queries are inaccurate, unless you do not care about consistent results - which is a quite rare situation.

Related Question