0

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?

  • Edit your question to include explain plans for each query – Philᵀᴹ Nov 25 '16 at 14:28
  • @Philᵀᴹ - added. – Kevin Meredith Nov 25 '16 at 14:34
  • Did you run both queries right after each other? If so then run the first one again too and check the time that it takes the second time. – Marco Nov 25 '16 at 14:55
  • 1
    I think you need to add at least minimal working example if you cannot post the real table structure and query. Currently, execution plans are identical (not surprising since tables have no indexes at all , full table scan is the only option). Also, check real execution plan with dbms_xplan.display_cursor passing sql_id rather than EXPLAIN – a1ex07 Nov 25 '16 at 15:09
  • If I had 8 LEFT JOIN's instead of the single left join rates_table rt on rt.a = o.a, would the ORDER BY have to do more work if placed at the end rather than within the inner SELECT? – Kevin Meredith Nov 25 '16 at 15:22
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.