I provide a simply query that, I think, resembles my real query.
I'm using Oracle.
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
select * from rates_table
The following query finds all
main_table where its
c, limiting to the first 1000 results. Then, it left joins
rates_table, getting the
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
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 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:
And the second's:
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?