Oracle分页查询
一、利用rownum，无order by(最优方案)
如下例查询出来5003行数据，然后扔掉了前面5000行，返回后面的300行。经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然。
SELECT *
FROM (SELECT ROWNUM AS rowno, t.*
FROM XXX t
WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
AND TO_DATE ('20060731', 'yyyymmdd')
AND ROWNUM <= 20) table_alias
WHERE table_alias.rowno >= 10;
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT *
FROM table_name) a
WHERE ROWNUM <= 40)
WHERE rn >= 21
其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。
二、有Order by的排序写法（次优方案）
经过测试，此方法随着查询范围的扩大，速度也会越来越慢。
SELECT *
FROM (SELECT tt.*, ROWNUM AS rowno
FROM ( SELECT t.*
FROM XXX t
WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
AND TO_DATE ('20060731', 'yyyymmdd')
ORDER BY create_time DESC, emp_no) tt
WHERE ROWNUM <= 20) table_alias
WHERE table_alias.rowno >= 10;
三、无Order by 排序的另一写法（不推荐）
此方法随着查询数据量的扩张，速度会越来越慢
SELECT *
FROM (SELECT ROWNUM AS rowno, t.*
FROM k_task t
WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
AND TO_DATE ('20060731', 'yyyymmdd')) table_alias
WHERE table_alias.rowno <= 20 AND table_alias.rowno >= 10;
四、有order by排序的写法（不推荐）
此方法随着查询范围的扩大，速度会越来越慢
SELECT *
FROM (SELECT tt.*, ROWNUM AS rowno
FROM ( SELECT *
FROM k_task t
WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
AND TO_DATE ('20060531', 'yyyymmdd')
ORDER BY fact_up_time, flight_no) tt) table_alias
WHERE table_alias.rowno BETWEEN 10 AND 20;
利用rownum分页可以参考下面的sql
select * from (
select t1.*,rownum rn from (
select * from scott.emp order by sal desc --这里是排序好的表
)t1 where rownum < 8 --这里是上限
)where rn > 5 --这里是下限
利用rowid分页可以参考下面的sql，rowid的效率比rownum高
select * from scott.emp where rowid in ( --这里选择要查询出的字段
select rid from (
select rownum rn,rid from (
select rowid rid,sal from scott.emp order by sal desc --这里进行表排序
) where rownum < 8 --这里是上限
) where rn > 5 --这里是下限
) order by sal desc --这里再次排序
