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

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?