Query optimization using Rownum in Oracle

Below are the sample queries which shows the implementation of rownum in ORACLE. These queries are well tested and implemented in various applications. Using this the developers can design better pagination techniques.

QUERY1:

select rn,cid,r_no,crdid,insname from ( SELECT row_number() over(order by c.id DESC) as rn,c.id as cid,0 as r_no,ip_info.crdid crdid,info.insuredname insname
from TABLE1 c inner join TABLE2 info on info.insid=c.insid
where statusflag in (4,91) and setflag=1
) where rn between 21 and 30;

QUERY2: This query is more optimized compared to QUERY1

In this query we are fetching the records from TABLE1 first with suitable criteria and then implementing JOIN.

SELECT c.id cid,0 as r_no,info.crid crdid,info.insuredname insname
FROM (SELECT * FROM (SELECT id FROM TABLE1
WHERE statusflag in (4,91) and setflag=1
ORDER BY id DESC)
WHERE ROWNUM between 21 and 30) c
INNER JOIN TABLE2 info ON info.insid=c.insid;