Why do these seemingly similar queries have such drastically different run times?

Question

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.

Answer 1

ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N, where N is the number of rows in the set ROWNUM is used with. In the first case, you are cutting the number of rows right off the bat, and in the second one you have to look for everything to cut off things that are bigger than 2.

Answer 2

My understanding is that the rownum assignment occurs after (or 'as') the row is selected, so any 'ROWNUM >= n' query with n greater than 1 is going to cause trouble. What was explained to me is that the first row is looked at; it is rownum 1, so it doesn't meet the criteria and is thrown away. The next row is looked at; it will still be rownum 1 since the result set is empty, and it doesn't meet the criteria and is thrown away. This process continues until all rows have been read and rejected.

Does the long-running query actually produce any data? Or have you always killed it before it completed?