oracle数据库分页查询

SELECT *

FROM (SELECT a.*, ROWNUM rn

FROM (SELECT *

FROM table_name) a

WHERE ROWNUM <= 40)

WHERE rn >= 21;

1.无ORDER BY排序的写法。(效率最高)
(经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！)
SELECT *

FROM (SELECT ROWNUM AS rowno, t.*

FROM emp t

WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

AND TO_DATE ('20060731', 'yyyymmdd')

AND ROWNUM <= 20) table_alias

WHERE table_alias.rowno >= 10;

2.有ORDER BY排序的写法。(效率较高)
(经过测试，此方法随着查询范围的扩大，速度也会越来越慢哦！)
SELECT *

FROM (SELECT tt.*, ROWNUM AS rowno

FROM (SELECT t.*

FROM emp t

WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

AND TO_DATE ('20060731', 'yyyymmdd')

ORDER BY create_time DESC) tt

WHERE ROWNUM <= 20) table_alias

WHERE table_alias.rowno >= 10;