Oracle的分页查询语句

最新推荐文章于 2021-03-30 11:24:15 发布

keepAction

最新推荐文章于 2021-03-30 11:24:15 发布

阅读量924

点赞数

分类专栏： other

other 专栏收录该内容

12 篇文章

订阅专栏

本文详细解析了Oracle分页查询的原理及优化方法，包括Rownum的使用技巧与性能比较，并探讨了Rownum与OrderBy查询的关系，以及在不同数据库环境下分页查询的实现方式。此外，文章还介绍了Hibernate实现分页查询的底层机制，通过实例展示了如何高效地进行数据分页操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。
分页查询格式：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

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

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

Oracle查询中rownum与Order by查询的关系

在开发应用的时候，把数据按照一定的规则排序后再取前几条数据这种操作是很平常的事情。我们在Oracle中常用的就是order by，然后取得rownum小于多少的数据这种方法。不过如果对Oracle不熟悉，也许就会发现你写的SQL语句检索出来的值不正确，这个是为什么呢。

因为Oracle在检索的时候，会首先把数据都检索出来，然后在排序段中进行排序。假如你有一个SQL语句如下所示。

SELECT

EMP_NO

FROM

EMP

WHERE

ROWNUM < 10

ORDER BY

EMP_NO

在检索的时候，会首先把数据检索出来，也就是把EMP表数据都检索出来，然后再Order by排序操作。因为ROWNUM 数据是在排序前就检索出来的了，所以不能利用ROWNUM来取得排序后的前10条操作。那么应该如何操作呢。其实很简单，利用子查询，先排序，再取 ROWNUM。如下所示

SELECT

EMP_NO

FROM

(

SELECT

EMP_NO

FROM

EMP

ORDER BY

EMP_NO

)

WHERE

ROWNUM < 10

这样你就会发现你最后可以检索到你需要的结果了
Hibernate 可以实现分页查询，例如：
　　从第2万条开始取出100条记录
　　Query q = session.createQuery("from Cat as c");
　　q.setFirstResult(20000);
　　q.setMaxResults(100);
　　List l = q.list();
　　那么Hibernate底层如何实现分页的呢？实际上Hibernate的查询定义在net.sf.hibernate.loader.Loader这个类里面，仔细阅读该类代码，就可以把问题彻底搞清楚。
　　Hibernate2.0.3的Loader源代码第480行以下：
　　if (useLimit) sql = dialect.getLimitString(sql);
　　PreparedStatement st = session.getBatcher().prepareQueryStatement(sql, scrollable);
　　如果相应的数据库定义了限定查询记录的sql语句，那么直接使用特定数据库的sql语句。
　　然后来看net.sf.hibernate.dialect.MySQLDialect:
　　public boolean supportsLimit() {
　　return true;
　　}
　　public String getLimitString(String sql) {
　　StringBuffer pagingSelect = new StringBuffer(100);
　　pagingSelect.append(sql);
　　pagingSelect.append(" limit ?, ?");
　　return pagingSelect.toString();
　　}
　　这是MySQL的专用分页语句，再来看net.sf.hibernate.dialect.Oracle9Dialect:
　　public boolean supportsLimit() {
　　return true;
　　}
　　public String getLimitString(String sql) {
　　StringBuffer pagingSelect = new StringBuffer(100);
　　pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
　　pagingSelect.append(sql);
　　pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
　　return pagingSelect.toString();
　　}
　　Oracle采用嵌套3层的查询语句结合rownum来实现分页，这在Oracle上是最快的方式，如果只是一层或者两层的查询语句的rownum不能支持order by。

讨论第2种：

先看以下两条语句的执行结果：

语句一：select rownum,empno,sal from emp order by empno;

    ROWNUM      EMPNO        SAL
---------- ---------- ----------
         1       7369        800
         2       7499       1600
         3       7521       1250
         4       7566       2975
         5       7654       1250
         6       7698       2850
         7       7782       2450
         8       7788       3000

　　……

语句二：select rownum,empno,sal from emp order by sal;

    ROWNUM      EMPNO        SAL
---------- ---------- ----------
         1       7369        800
        12       7900        950
        11       7876       1100
         3       7521       1250
         5       7654       1250
        14       7934       1300
        10       7844       1500
         2       7499       1600

……

同样的两个语句，执行结果中的rownum伪列的值却大相径庭，这是什么原因呢？

其实这都是ORACLE内部的查询优化器和索引搞的鬼！

rownum伪列产生的序号是按照数据被查询出来的顺序添加上去的，第一条是1,第二条是2,依次加1。

当将一条语句交给查询优化器处理时：

如果排序列上有索引，则借助索引去查询数据，这样，读取出来的数据和rownum产生的序号是一种正常的对应关系，如语句一的结果（empno上有主键索引）。

如果排序列上没有索引，则使用全表扫描的方式，依次从表中读取数据，读取完成后，最后进行排序，于是产生了类似语句二的结果（sal列上没有索引）。

正是由于排序列上不一定有索引，所以在ORACLE中使用rownum伪列分页时，需要多加一层查询，以保证rownum序号的连续性。

语句三：select rownum,t.* from
(select empno,sal from emp order by sal) t;

    ROWNUM      EMPNO        SAL
---------- ---------- ----------
         1       7369        800
         2       7900        950
         3       7876       1100
         4       7521       1250
         5       7654       1250
         6       7934       1300
         7       7844       1500
         8       7499       1600

……

这个结果还满意吧。。。

分页：在外面再加上一层查询。

select * from
(select rownum num,t.* from
(select empno,sal from emp order by sal) t)
where num between 6 and 10;

当然，如果使用分析函数row_number就可以省略一层查询了，代码更简单点：

select * from
(select row_number() over (order by sal) num,empno,sal from emp)
where num between 6 and 10;

sql server 2000分页情况：
第一种：
select top 10 *
from DataComplete
where a_id not in (
select top 0 a_id from DataComplete order by a_id ) and a_id>2100
order by a_id

10代表pageSize
0代表page.getPageSize()*(page.getCurPage()-1)
2100条件限制。
java中拼凑sql语句：
sqlbuf.append("select top ");
  sqlbuf.append(page.getPageSize());
  sqlbuf.append(" "+sb.toString());
  sqlbuf.append(" from ");
  sqlbuf.append(tableName);
  sqlbuf.append(" where a_id not in (select top ");
  if(page.getCurPage() < 1) page.setCurPage(1);
  sqlbuf.append(page.getPageSize()*(page.getCurPage()-1));
  sqlbuf.append(" a_id ");
  sqlbuf.append(" from ");
  sqlbuf.append(tableName);
  sqlbuf.append(" order by a_id ) and a_id>? order by a_id");
  System.out.println("<DatacenterDao::generateQuerySQL>sql:"+sqlbuf.toString());

第二种：
select * from (
select (select count(*) from DataComplete p1
where p1.a_id <= p2.a_id and a_id >2000 ) as rownum,*
from DataComplete p2
where a_id >2000 ) ResultSet
where rownum >= 1 and rownum <= 2

第二种不能排序，第一种比较容易理解。

sql server 2005分页情况