Oracle中慎用带有order by的分页

Hibernate处理Oracle的分页，是用rownum。

代码如下：

  public String getLimitString(String sql, boolean hasOffset); 
  {
    StringBuffer pagingSelect = new StringBuffer(sql.length(); + 100);;
    if (hasOffset); {
      pagingSelect.append(
          "select * from ( select row_.*, rownum rownum_ from ( ");;
    }
    else {
      pagingSelect.append("select * from ( ");;
    }
    pagingSelect.append(sql);;
    if (hasOffset); {
      pagingSelect.append(" ); row_ where rownum <= ?); where rownum_ > ?");;
    }
    else {
      pagingSelect.append(" ); where rownum <= ?");;
    }
    return pagingSelect.toString();;
  }

出错前提

如果这里的sql是不带order by的sql，则查询结果没有任何问题。

但是，如果sql中带有order by，则会引起混乱，即相同记录会出现在不同页中。但是，这种混乱的出现通常是在下面的情况下：

1、纪录数足够多（如果表中有lob字段更好：P）

2、插入记录数大于3页，每页最好10＋条记录

3、order by字段至少需要有2个值

4、具备相同order by字段的记录数大于3页

5、插入记录后，最好做删除、修改操作，然后再插入记录。保证记录在磁盘环境中的顺序是无序的。

6、如果满足上述条件，但还没有出现混乱现象，则适当的加大纪录数。

大家测试这种现象，不需要通过hibernate，直接通过sql就可查到。下面有由hibernate生成的sql，以供大家试验：

select * from
  ( select row_.*, rownum rownum_ from
     ( select * from T_TABLE tTable where tTable.field1 order by  tTable.field2 desc ) row_ where rownum <= ?) where rownum_ > ?;

错误原因

之所以出现这样的问题，是和oracle处理ROWNUM的原理相关的。

以下是Oracle参考手册上的一段话：


解决办法（来自使用手册）：

9.3.3. Scrollable iteration
If your JDBC driver supports scrollable ResultSets, the Query interface may be used to obtain a ScrollableResults which allows more flexible navigation of the query results. (Oracle 8.1.6+)

Query q = sess.createQuery("select cat.name, cat from DomesticCat cat " +
                            "order by cat.name");;
ScrollableResults cats = q.scroll();;
if ( cats.first(); ); {

    // find the first name on each page of an alphabetical list of cats by name
    firstNamesOfPages = new ArrayList();;
    do {
        String name = cats.getString(0);;
        firstNamesOfPages.add(name);;
    }
    while ( cats.scroll(PAGE_SIZE); );;

    // Now get the first page of cats
    pageOfCats = new ArrayList();;
    cats.beforeFirst();;
    int i=0;
    while( ( PAGE_SIZE > i++ ); && cats.next(); ); pageOfCats.add( cats.get(1); );;

}

我觉得，如果处理数据库类是一个统一的基类，这种方法不适合用。

11.13 Tips & Tricks

1、Collection elements may be ordered or grouped using a query filter:

Collection orderedCollection = s.filter( collection, "order by this.amount" );;
Collection counts = s.filter( collection, "select this.type, count(this); group by this.type" );;

2、Collections are pageable by using the Query interface with a filter:

Query q = s.createFilter( collection, "" );; // the trivial filter
q.setMaxResults(PAGE_SIZE);;
q.setFirstResult(PAGE_SIZE * pageNumber);;
List page = q.list();;

这种方法的效率有待考察。

所有sql不用ROWNUM的解决办法

如果执行所有SQL都不用ROWNUM，那么最简单的办法如下：

1、派生Dialect类

package com.xxx.data.db.hibernate;

import  net.sf.hibernate.dialect.Oracle9Dialect;

public class Oracle9ThunisoftDialect extends Oracle9Dialect
{
  public Oracle9ThunisoftDialect();
  {
    super();;
  }

  public boolean supportsLimit();
  {
    return false;
  }
}

2、修改hibernate.cfg.xml配置文件

 <property name="hibernate.dialect">com.xxx.data.db.hibernate.Oracle9ThunisoftDialect</property>

<!-- oracle 8.1.6+ -->
<property name="hibernate.jdbc.use_scrollable_resultset">true</property>

后记

通常情况下，这种现象很少出现，因为我们程序中缺省的order by字段的“选择性”都很大，比如缺省以日期时间排序，order by字段很少出现重复。上面的现象基本上不会出现。不过，我们公司在做压力测试的时候，同一日期时间的记录插入了N多条，从而满足了上面提到的5个前提条件，因此出现了同一记录在不同页中出现的现象。

备注：
感谢Readonly对于派生Dialect的提示

扩展一下Dialect
把supportsLimit改成false
这样可以使用ResultSet的定位。

Robbin 的文章解释得很清楚了：
http://www.hibernate.org.cn/58.html

3ku，i will try it:)

他这里解释的是hibernate在不同数据库中是怎么分页的，并没有提到带order by的分页，在oracle中会出错。

文中提到的

好像是说现在的三层可以order by，如果两层就不行吧？

这和我说的意思是完全不同的，我的意思是即使是三层，也会出错。我是从oracle实现角度阐明了出错原因：

我有一个疑问：虽然rownum是在order by之前已经安排好了，但它是不是指仅在一个视图中呢？如果是的话，那这个语句一点都没有错，因为它用的是row_视图的rownum。但真是出错了（其实就是顺序有可能不一样），应该是它的排序算法有问题(或者是其它的问题，例如：缓存，或者XXX）。

当然如果rownum是不重新分配的话(意思是指在一整个查询中），那就没有得说啦。

实践出真知，试一下就知道结果了。我说出错，既有oracle理论指导，又有自己试验的结果。

另外，你可以看一下上面提到的hibernate手册中的scroll的示例，似乎也在表示order by的时候，怎么分页。

哈哈，我只是想知道"虽然rownum是在order by之前已经安排好了，但它是不是指仅在一个视图中呢？"而罢了。

你这个实践也不能说明这个问题呀！

我能告诉你的就是，结果会出现错误。

至于错误原因，很可能有多种。

首先，就是oracle手册上说的：“即使select语句中一条简单的order by都可能会搞乱ROWNUM（因为ROWNUM是排序前分配给各行的） ”。

至于你的疑问，我感觉，是oracle自己对视图的实现机制问题。oracle有它的优化策略，我们认为的执行策略，并不代表它的执行方式。

所以，为了保证程序的健壮性，最好还是谨慎处理比较好。

嗯，这点很同意楼上，嘻嘻。

恩
会出些莫名其妙的错误