传统的分页是这样的
select * from (select rownum rw, * from table_name order by key_column) where rw >= ? and rw < ?当表比较小时这样做还可以接受,但是表比较大时就有问题了select rownum rw, * from table_name order by key_column
这一语句运行比较缓慢并且消耗资源较多怎么优化好?
select * from (select rownum rw, * from table_name order by key_column) where rw >= ? and rw < ?当表比较小时这样做还可以接受,但是表比较大时就有问题了select rownum rw, * from table_name order by key_column
这一语句运行比较缓慢并且消耗资源较多怎么优化好?
order by key_column) where rw >= ?
select * from (select rownum rw, * from table_name where rw < ? order by key_column) where rw >= ?
select *
from (select a.*,row_number() over(order by key_column) rw
from tb a)
where rw >= ? and rw < ?select *
from (select a.*,rownum rw from (select * from table_name order by key_column) a
where rownum <m)
where rw>=n
--你的的这样
select *
from (select a.*,rownum rw from (select * from table_name order by key_column) a
)
where rw >= ? and rw < ?
(
select rownum rw, t.* from
(select * from table_name order by key_column) t
where rownum < ?
)
where rw > ?
select *
from (select a.*,rownum rw from (select * from table_name order by key_column) a
where rownum <m)
where rw>=n
=========
这个写法比较快 但也要一分多钟
起因是我的分页sql让dba杀了
另外一种写法是between,显然没这个快。
如果还需要追求速度,我觉得可能要考虑其它的方法了,单单的去优化分页查询语句,没什么可优化的了。