Why do these seemingly similar queries have such drastically different run times?
- by Jherico
I'm working with an oracle DB trying to tune some queries and I'm having trouble understanding why working a particular clause in a particular way has such a drastic impact on the query performance. Here is a performant version of the query I'm doing
select * from
(
select a.*, rownum rn from
(
select *
from table_foo
) a where rownum < 3
) where rn >= 2
The same query by replacing the last two lines with this
) a where rownum >=2 rownum < 3
)
performs horribly. Several orders of magnitude worse
) a where rownum between 2 and 3
)
also performs horribly. I don't understand the magic from the first query and how to apply it to further similar queries.
