使用rownum进行分页的探讨

关于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来提供唯一顺序；

第三种：  


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

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

第四种：

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


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