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.