Question

jdbctemplate怎么实现oracle分页查询

Answer 1

sql语句如下：
分页1
SELECT *
FROM (Select ROWNUM AS ROWNO, T.*
from 表名 T(别名)
where 表字段 between to_date('20060501', 'yyyymmdd') and to_date('20060731', 'yyyymmdd')
AND ROWNUM <= 20) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO >= 10;
经过测试，此方法成本最低，只嵌套一层，速度最快,即使查询的数据量再大，也几乎不受影响，速度依然.

分页2：
SELECT *
FROM (SELECT TT.*, ROWNUM AS ROWNO
FROM (Select t.*
from 表名 T(别名)
where flight_date between to_date('20060501', 'yyyymmdd') and
to_date('20060531', 'yyyymmdd')
ORDER BY FACT_UP_TIME, flight_no) TT(别名二)
WHERE ROWNUM <= 20) TABLE_ALIAS
where TABLE_ALIAS.rowno >= 10;
经过测试，此方法随着查询范围的扩大，速度也会越来越慢,