oracle分页查询原理

原创已于 2023-12-27 11:27:49 修改 · 991 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

于 2015-12-11 10:23:41 首次发布

java 专栏收录该内容

525 篇文章

订阅专栏

分页的原理：

1.借助ORACLE的rownum这个字段的特性查询。

2. 查询前N条数据

3.从第2步的查询结果中取M到N条数据

4.在web开发中将M，N作为参数进行传递，就可以得到分页的结果。

例子：查询表中20~30条记录，sql语句如下。

select * from(

select rownum as pageNo, A.* from tableName A where rownum <= 30) B where B.pageNo >=20;

Oracle分页查询的原理主要依赖于ROWNUM伪列。以下是Oracle分页查询的详解：

ROWNUM的使用：
- ROWNUM是Oracle系统为查询返回的行分配的一个编号。它返回的第一行是1，第二行是2，依此类推。
- 需要注意的是，ROWNUM总是从1开始，并且不能用于等于判断，只能用于小于或小于等于。例如，使用ROWNUM = 1这样的条件将永远不会得到匹配的行。
分页查询的方法：
- 第一种方法（推荐）：首先，从主查询中获取需要的数据，然后在子查询中利用ROWNUM进行分页。这样可以避免在排序后进行全选，从而提高性能。例如：
```
sql`SELECT * FROM (  
  SELECT a.*, ROWNUM rn FROM (  
    SELECT * FROM table_name WHERE ... ORDER BY ...  
  ) a WHERE ROWNUM <= #{pageNo} * #{pageSize}  
) WHERE rn > #{pageNo - 1} * #{pageSize} + 1;`
```
- 第二种方法：直接在主查询中加入排序和分页条件。但是这种方法可能需要对内层查询的结果集进行全排序，从而影响性能。例如：
```
sql`SELECT * FROM (  
  SELECT a.*, ROWNUM rn FROM table_name a WHERE ... ORDER BY ...  
) WHERE rn > #{pageNo - 1} * #{pageSize} + 1 AND rn <= #{pageNo} * #{pageSize};`
```
排序在分页中的影响：如果查询中包含排序操作，那么Oracle会按照排序的顺序为行分配ROWNUM值。这意味着，如果数据量很大，排序操作可能会增加分页查询的执行时间。为了提高性能，可以考虑在子查询中进行排序操作。
复杂查询的处理：当查询复杂时，可以考虑在子查询中加入过滤条件和排序操作，然后再进行分页。这样可以减少需要处理的数据量，提高性能。
注意事项：使用ROWNUM进行分页时，要确保排序操作在子查询中完成，以避免不必要的性能损失。同时，要注意查询的性能问题，特别是在处理大量数据时，考虑使用索引、优化查询语句和数据库结构等手段来提高性能。

总之，Oracle的分页查询原理主要依赖于ROWNUM伪列。通过合理地使用子查询和排序操作，可以实现高效的分页查询。在进行分页查询时，还要注意查询的性能问题，并采取相应的优化措施来提高查询效率。

如果你想在Java中使用Oracle数据库进行分页查询，你可以创建一个工具类来封装常用的分页查询逻辑。以下是一个简单的Oracle分页查询工具类的示例：

import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
  
public class OraclePaginationHelper {  
    private static final int DEFAULT_PAGE_SIZE = 10;  
    private static final int DEFAULT_OFFSET = 0;  
  
    public static List<Object[]> queryForPage(Connection connection, String query, int pageNo, int pageSize) throws SQLException {  
        int offset = (pageNo - 1) * pageSize;  
        String pagedQuery = query + " fetch first " + pageSize + " rows only offset " + offset + " rows";  
        PreparedStatement pstmt = connection.prepareStatement(pagedQuery);  
        ResultSet rs = pstmt.executeQuery();  
        List<Object[]> resultList = new ArrayList<>();  
        while (rs.next()) {  
            Object[] row = new Object[rs.getMetaData().getColumnCount()];  
            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {  
                row[i - 1] = rs.getObject(i);  
            }  
            resultList.add(row);  
        }  
        return resultList;  
    }  
  
    public static List<Object[]> queryForPage(Connection connection, String query) throws SQLException {  
        return queryForPage(connection, query, 1, DEFAULT_PAGE_SIZE);  
    }  
}

这个工具类提供了两个静态方法：queryForPage。第一个方法接受一个数据库连接、查询字符串、页码和每页的记录数作为参数，并返回分页查询的结果列表。第二个方法是一个简化版本，它使用默认的页码和每页的记录数进行查询。

你可以在你的应用程序中调用这个工具类，并传入相应的参数来执行分页查询。例如：

try (Connection connection = DriverManager.getConnection(dbUrl, username, password)) {  
    List<Object[]> resultList = OraclePaginationHelper.queryForPage(connection, "SELECT * FROM my_table WHERE ...", 2, 10);  
    // 处理查询结果...  
} catch (SQLException e) {  
    // 处理异常...  
}