oracle高效分页查询总结

2017-12-30 11:35:31来源:oschina作者:xiaomin0322人点击

分享
oracle高效分页查询总结

本文参考链接:http://blog.sina.com.cn/s/blog_8604ca230100vro9.html


探索查询语句:


复制代码

--分页参数:size = 20 page = 2 --没有order by的查询 -- 嵌套子查询,两次筛选(推荐使用) --SELECT * -- FROM (SELECT ROWNUM AS rowno, t.* -- FROM DONORINFO t -- WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') -- AND TO_DATE ('20060731', 'yyyymmdd') -- AND ROWNUM <= 20*2) table_alias -- WHERE table_alias.rowno > 20*(2-1); --耗时0.05s
-- 一次筛选(数据量大的时候,第一次查询的数据量过大,明显比上面慢,不推荐) --select * from( --SELECT ROWNUM AS rowno, t.* --FROM DONORINFO t --WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') AND TO_DATE ('20060731', 'yyyymmdd') --) r --where r.rowno BETWEEN 20*(2-1)+1 and 20*2; --耗时0.46s--有order by的查询 --嵌套子查询,两次筛选(推荐使用) --SELECT * --FROM (SELECT ROWNUM AS rowno,r.* -- FROM( -- SELECT * FROM DONORINFO t -- WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') -- AND TO_DATE ('20060731', 'yyyymmdd') -- ORDER BY t.BIRTHDAY desc -- ) r -- where ROWNUM <= 20*2-- ) table_alias -- WHERE table_alias.rowno > 20*(2-1); --耗时0.744s
-- 一次筛选(数据量大的时候,第一次查询的数据量过大,明显比上面慢,不推荐) --select * from ( --SELECT ROWNUM AS rowno,r.* --FROM( --SELECT * FROM DONORINFO t --WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') --AND TO_DATE ('20060731', 'yyyymmdd') --ORDER BY t.BIRTHDAY desc --) r ----where ROWNUM <= 20; --这里用>查不到数据 =也查不到数据 <= 或者 < 可以查到数据----where ROWNUM BETWEEN 20*(2-1)+1 AND 20*2; --查不到数据 ----where ROWNUM <=20*2 and ROWNUM > 20*(2-1); --查不到数据 ----这是因为查询时,第一条生成的rownum为1,1>20不成立,1=20也不成立,所以这条数据就作废了,依次类推,这样就查不到任何一条数据 --) t--where t.rowno <=20*2 and t.rowno > 20*(2-1); --可以查到数据耗时:3.924s ---- where t.rowno BETWEEN 20*(2-1)+1 AND 20*2; --可以查到数据耗时:3.919s
--采用row_number() over 分页函数 --select *--from(select d.*,row_number() over(order by d.BIRTHDAY) as rownumber-- from DONORINFO d -- WHERE d.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') -- AND TO_DATE ('20060731', 'yyyymmdd') -- ) p--where p.rownumber BETWEEN 20*(2-1)+1 AND 20*2; --耗时0.812s
select * from ( select *
from(select d.*,row_number() over(order by d.BIRTHDAY) as rownumber from DONORINFO d WHERE d.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') AND TO_DATE ('20060731', 'yyyymmdd') ) p where p.rownumber <20*2 ) where rownumber > 20*(2-1);-- 耗时0.813s

复制代码


从以上探索比较,我们得知:


1、ROWNUM


  rownum总是从1开始的,第一条不满足去掉的话,第二条的rownum 又成了1。依此类推,所以永远没有满足条件的记录。


可以这样理解:rownum是一个序列,是Oracle数据库从数据文件或缓冲区中读取数据的顺序。


它取得第一条记录则rownum值为1,第二条为2。依次类推。


当使用“>、>=、=、between...and”这些条件时,从缓冲区或数据文件中得到的第一条记录的rownum为1,不符合sql语句的条件,会被删除,接着取下条。


下条的rownum还会是1,又被删除,依次类推,便没有了数据。


 所以上限条件必须放在子查询,而下限条件必须放在外层查询。


2、between and 和 >= and <=


这两者查询效率上来说没有区别,between and 最终也是转为>= and <=


所以select * from (select * from a where a.time >= to_date('19920324','yyyymmdd')) b where b.time <=to_date('20170324','yyyymmdd')


这样的嵌套是没有必要的,可以直接用between and。


3、Oracle通用分页格式


对于没有order by语句的分页:


复制代码

SELECT *
FROM (SELECT ROWNUM AS rowno, t.*
FROM DONORINFO t WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') AND TO_DATE ('20060731', 'yyyymmdd') AND ROWNUM <= page*size) table_alias WHERE table_alias.rowno > (page-1)*size;

复制代码

有order by语句的分页


复制代码

SELECT *
FROM (SELECT ROWNUM AS rowno,r.*
FROM(SELECT * FROM DONORINFO t WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') AND TO_DATE ('20060731', 'yyyymmdd') ORDER BY t.BIRTHDAY desc ) r where ROWNUM <= page*size ) table_alias WHERE table_alias.rowno > (page-1)*size;

复制代码

另外我们也可以使用row_number() over函数:


复制代码

select*from(select d.*,row_number() over(orderby d.BIRTHDAY) as rownumber from DONORINFO d WHERE d.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') AND TO_DATE ('20060731', 'yyyymmdd') ) p where p.rownumber BETWEEN size*(page-1)+1AND page*size; http://www.cnblogs.com/jay763190097/p/6813792.html

复制代码

但是相比前面的并没有什么优势。

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台