Why is my SQL query slower when I specify ROWNUM, than when I specify date range in WHERE clause

user_9014011 0
Viewed 108

I'm trying to achieve pagination of a large dataset. I figured this might work

select asortyment.nazwa as NAZWA,
  parpoz.ilosc as ILOSC,
  parpoz.cenabrutto as CENA,
  parnagl.data as DATA
from parnagl

inner join ParPoz on ParPoz.ParNaglID=ParNagl.ParNaglID 
inner join asortyment on asortyment.poztowid=parpoz.poztowid 
inner join grupapt on asortyment.grupaptid=grupapt.grupaptid
WHERE ROWNUM <= 10

This takes 12 seconds to perform. On the other hand, if I specify date range using WHERE clause, it only takes 50ms... Is there any way I could optimise this?

The database is Oracle 11g.

query-performance oracle11g oracle sql
1 Answers

There's too little information to answer the question. Without the schema, it's hard to guess what the QO might be doing. Without the schema, we still might make progress if we had the execution plan. But the execution plan isn't given, either.

We have to remember that without a limiting WHERE clause, the correct execution of the query mandates that the database might need to exhaustively try to produce all available rows. Then, it will produce only the first 10 ... whatever those might be since there's no ordering given.

But with a limiting WHERE clause, the query plan might not demand such a wide-open execution plan to be correct. In fact, it can first limit itself to finding rows that match the date range in the first place. Depending on the index structure, filtering the dates first might be immensely faster.

What if 2 billion rows satisfy the unlimited JOIN without a date specified?

And what if just 2 rows match only the date range specified, and then only one of those two fits through the JOIN criteria?

Which of the two queries do you think would execute faster?

So I guess the next piece of information missing is apparent: What do you want the query to do? Using a ROWNUM limit without an ordering is pretty weird, but maybe you have an interesting case where it is useful. OTOH, if using the date range is faster ... why not use the date range?

user_18582410
Related