Oracle的分页实现（代码教程）

在oracle中实现分页的方法大致分为两种，用rownum关键字和用rowid关键字，下面来详细介绍一下：

1、rownum

其代码为：

select *
  from (select row_.*, rownum rownum_
          from (select *
                  from table1
                 where table1_id = xx
                 order by gmt_create desc) row_
         where rownum <= 20)
 where rownum_ >= 10;

这应该是我们大部分程序里所用到的版本，因为这个版本很容易实现复用，中间row_部分，就是我们平常写到的sql语句，然后再将起始条数和终止条数作为专门的分页sql语句传入即可查询出我们想要的结果。

    从效率上看，上面的sql语句在大多数情况拥有较高的效率，主要体现在where rownum <= 20这句上，这样就控制了查询过程中的最大记录数，而在查询的最外层控制最小值。但最大值意味着如果查到了很大的范围（如百万级别的数据），查询就会从很大范围内往里减少，效率就会很低，因此，当面对大数据量时或者优化查询效率时，如果你用了rownum，可以换第二种方法。

由以上的方法，又可以引申出3种方式：

a、结合between and

代码如下：

select *
  from (select a.*, rownum rn
          from (select *
                  from table1
                 where table1_id = xx
                 order by gmt_create desc) a)
 where rn between 10 and 20;

这个就是换汤不换药了，而且查询效率更低，因为：

    oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率，但不能跨越多层。

由于查询条件between 10 and 20是存在于查询的第三层，而oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道rn代表什么）。因此，这个查询语句，oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比原始的查询低得多。

b、结合minus

select *
  from table1
 where rownum <= 20
minus
select * from table1 where rownum <= 10;

查询了两次，效率上更差了一些。

c、row_number() over( order by order_date desc)

这个和rownum关键字类似，生成的顺序和rownum的语句一样，效率也一样（对于同样有order by 的rownum语句来说），所以在这种情况下两种用法是一样的。

而对于分组后查询做分页的话，则是rownum无法实现的，这时只有row_number可以实现，row_number() over(partition by 分组字段 order by 排序字段)就能实现分组后编号，其代码为：

select *
  from (select a.*,
               row_number() over(partition by trunc(order_date) order by order_date desc) rn
          from table1 a)
 where rn <= 10;

2、rowid

rowid仍旧需求rownum，但方式不同，因此我将其归为另一大类，其代码为：

select *
  from (select rid
          from (select r.rid, rownum linenum
                  from (select rowid rid
                          from table1
                         where table1_id = xx
                         order by order_date desc) r
                 where rownum <= 20)
         where linenum >= 10) t1,
       table1 t2
 where t1.rid = t2.rowid;

从语句上看，共有4层select嵌套查询，最内层为可替换的不分页原始sql语句，但是他查询的字段只有rowid，而没有任何待查询的实际表字段，具体查询实际字段值是在最外层实现的；

这种方式的原理大致为：

    首先通过rownum查询到分页之后的10条实际返回记录的rowid，最后通过rowid将最终返回字段值查询出来并返回；

和前面rownum实现方式相比，该sql的实现方式更加繁琐，通用性也不是非常好，因为要将原始的查询语句分成两部分（查询字段在最外层，表及其查询条件在最内层），想要复用就很困难了；

但这种实现在特定场景下还是有优势的：比如我们经常要翻页到很后面，比如10000条记录中我们经常需要查9000-9100及其以后的数据；此时该方案效率可能要比前面的高；

因为前面的方案中是通过rownum <= 9100来控制的，这样就需要查询出9100条数据，然后取最后9000-9100之间的数据，而这个方案直接通过rowid取需要的那100条数据；

从不断向后翻页这个角度来看，第一种实现方案的成本会越来越高，基本上是线性增长，而第三种方案的成本则不会像前者那样快速，他的增长只体现在通过查询条件读取rowid的部分；

因此，在我们实际项目中，基本分页都是可以单靠rownum就可以实现，而在数据量只有几十万的情况下，效率也是够的，如果一定要优化，则可以考虑rowid。