公司Oracle分页模板探究

在第一家公司实习时曾需要解决这样一个问题：从百万数量级的日志中分页查询按时间排序的日志，当时实现的结果是查询非常非常慢，而且越是后面的页就越慢。现在想来，当时忽略了两个问题：

    ①排序问题，我当时强烈主张排序应该去掉，通过在时间字段上增加了索引，已经可以保证查询结果按时间顺序排列，没有必要加；另一方面，对百万数量级的日志进行排序，消耗可想而知。

    ②分页查询方法问题，当时采用的是rownum的分页方法，如下：

    select * from

        (select *, rn from tableName where rownum<#endRow#)

    where rn>#startRow#

    假设页号为page、页大小为pageSize，查询第page页会在内层子查询中返回page*pageSize条记录，所以当页数越大时，子查询要返回的记录数就越多，当面对上述百万数量级的查询时，子查询的时间消耗和内存消耗是相当难以接受的。

 

所以出了排序的问题以外，使用rowid来进行分页查询在大数据量的情形下会获得较好的效果，下面先介绍使用方法：

    select *

        from (

            select rid

                 from (

                     select rid, rownum as rn from(

                          select l1.rowid as rid from table1 l1

                      )where rownum<=page*pageSize

            )where rn>=(page-1)*pageSize+1

         )t1,table1 t2 where t1.rid=t2.rowid  

内层子查询只查询返回rowid，最后再外层通过rowid获得相应的记录，因此查询在大数据量的分页查询中，第二种方法的性能会明显高于第一种。

关于rowid和rownum伪列，rownum和数据的组织方式相关，它不能唯一标识记录，如果你没有定义主键或能唯一标识记录的字段的话，唯一标识记录的是rowid，插入一条记录后就为有个唯一的rowid来标识，且rowid不会再改变。但是如果你的表改变了表空间的话，rowid也可能会改变。rowid与rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中是唯一的。只要记录没被搬动过，rowid是不变的。rowid可以通过base64解码函数解码获得记录的存储信息：

SQL> select rowid ,
  2          B64.Base64_2Dec(substr(rowid,1,6)) object_no ,
  3          B64.Base64_2Dec(substr(rowid,7,3)) rel_file_id,
  4          B64.Base64_2Dec(substr(rowid,10,6)) block_no,
  5          B64.Base64_2Dec(substr(rowid,16,3)) row_no
  6  from emp a
  7  where rownum <= 5
  8  /
reference：http://jametong.itpub.net/post/5042/25513