Oracle优化

使用rownum进行分页的探讨

时间:2013-7-20 9:50:20  作者:说歌社区  来源:www.solgle.com  查看:185  评论:0
内容摘要:关于oracle查询分页,大家可能都擅长使用rownum,对于下面的查询你能看出,两个结果,哪个更吗?第一种:select rownum as rowno, a.* from (select * from (select * ...
关于oracle查询分页,大家可能都擅长使用rownum,对于下面的查询你能看出,两个结果,哪个更吗?
 
第一种:
select rownum as rowno, a.*
  from (select * 
          from (select * 
                  from table1
                 where col1=0 
                   and col2=2 
                 order by col1 desc) 
         where rownum<=30) a  
 where rownum>=20;
 
基于查询优化的考虑,可能你觉得第一种可以提前减少数据量,是个好的写法;
 
 
第二种:
select rownum as rowno, a.*
  from (select * 
          from table1
         where col1=0 
           and col2=2 
         order by col3 desc) a  
 where rownum>=20 and rownum <= 30';
 
小结:这个查询其实第一种返回的分页结果是不对的,分页记录有重复数据显示,得通过测试才能看出来,通过页面分页查询测试,第二种页面返回数据正确,且数据量少的时候(几十万)速度要比第一种快的多;所以要正确使用rownum,不能想当然的...

    用了一段时间,数据量上来了,现在是数据已经超过了1000万,分页查询反应速度就非常慢了,一页数据就大约20到200条不等,翻页显得相当慢;通过查询计划分析,其存在全表扫描,我们又不得不回到第一种可以缩小数据量的写法上来,可是数据存在重复的情况,不得不继续找找原因;
    通过分析,比较,得出,其第一种方式排序结果不是唯一的,因为排序字段存在重复情况,每页数据就可能是出自不同的排序数据中分页出来的数据,在上一页出现了,可能还会在下一页出现;有人遇到该问题采用了使用主键或者唯一索引来排序统一排序顺序,也有人采用了多列排序或rowid来提供唯一顺序;

第三种:  
select rownum as rowno, a.*
  from (select * 
          from (select * 
                  from table1
                 where col1=0 
                   and col2=2 
                 order by pk_col desc) 
         where rownum<=30) a  
 where rownum>=20;

select rownum as rowno, a.*
  from (select * 
          from (select * 
                  from table1
                 where col1=0 
                   and col2=2 
                 order by col1 desc,rowid asc) 
         where rownum<=30) a  
 where rownum>=20;

这样数据顺序是统一,但是在多列排序及加入rowid排序时,并没有提供很高的查询效率;

还有先提取符合条件的rowid,然后再提取出对应rowid的数据行,这种写法速度又显得快些,但是仍然还是没有起到很快速度,特别是在数据量上来以后。

第四种:
select b.rn,a.col1,a.col2,a.col3
  from (select rid,rn
          from (select rid,rownum rn             
                  from (select rowid rid
                          from table1 a
                         where col1='***'                             
                         order by col3 desc
                       )
                 where rownum<=60000
               )
         where rn>=59000         
       ) b,
       table1 a
 where a.rowid=b.rid;

这个先取rowid的写法,通过测试,速度比前面的几种要快些,特别是有了缓存过后,上千万的数据也可以秒出,不过第一次运行还是有所等待,且这钟写法不是很通用,不是很好理解,通用性最强的还是要属第三种,结构简单,数据上千万以后,有了缓存还是可以秒出,这个视乎有回到了我们当初讨论的问题。其实后来仔细分析,这个已经不是优化语句上面的问题了,而是使用逻辑上面的问题,想一想,一千万的数据,每页显示100条记录,也有10万页数据,你觉得谁会一页一页去看呢?我们就不应该拿整个表的记录来做分页,应该默认只提取出最需要的数据,或者最新的数据显示出来就可以了,这也符合我们的业务逻辑;对于其他没有包括在内的数据,可以再次改变条件(时间段,范围)查询,但是要注意数据量;

select rownum as rowno, a.*
  from (select * 
          from (select * 
                  from table1
                 where col1=0 
                   and col2=2 
                   and date1>=sysdate-2  /*比如只返回最近三天的数据*/
                 order by pk_col desc) 
         where rownum<=30) a  
 where rownum>=20;

pk_col1为主键或者唯一索引列,也可以采用能实现唯一排序的多个列,date1有必要创建索引,提高检索效率;
 

标签:使用rownum分页 rownum分页数据重复 

solgle.com 版权所有,欢迎分享!!!

相关文章
    相关评论
       Copyright © 2013-2020 solgle.com,All rights reserved.[solgle.com] 公安机关备案号:51010802000219
    Email:solgle@solgle.com; weixin:cd1008610000 ICP:蜀ICP备14011070号-1