Oracle分页

SqlServer、mysql、Oracle分页技术：

SqlServer分页语句：

select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id

mysql分页语句：

select * from articles limit " + (pageNo-1)*pageSize + "," + pageSize;

Oracle分页语句：

SELECT * FROM

(SELECT A.*, ROWNUM RN

FROM (SELECT * FROM TABLE_NAME) A

WHERE ROWNUM <= 40)

WHERE RN >= 21

 

或

SELECT * FROM

(SELECT A.*, ROWNUM RN

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

 

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

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

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

 

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

 

CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。

因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：

 

SELECT /*+ FIRST_ROWS */ * FROM

(SELECT A.*, ROWNUM RN

FROM (SELECT * FROM TABLE_NAME) A

WHERE ROWNUM <= 40)

WHERE RN >= 21

 

Oracle中其他分页方法：

1.根据rowid来分：

 SQL> select * from passvehicleinfo p where rowid in ( select rid from (select rownum rn,rid from (select p.rowid rid,p.passvehicleid from passvehicleinfo p order by p.passvehicleid desc) view1 where rownum<10000) view2 where rn >9980) order by p.passvehicleid asc;

2.按分析函数来分

 SQL> select * from (select p.*,row_number() over (order by p.passvehicleid desc ) rk from passvehicleinfo p) where rk>9980 and rk<10000;

3.按rownum来分

SQL> select * from (select view1.*,rownum rn from (select p.* from passvehicleinfo p order by p.passvehicleid desc) view1 where rownum<10000) view2 where rn>9980;

 

 

编写存储过程（省略了查询条件）

Sql代码

create or replace procedure P_Pagination( 

       page  int,--第几页

       perPageCount int ,--每页几条记录

       totalPage out int,--总页数

       pageResultSet out SYS_REFCURSOR --当前页查询出来的结果集  

) 

as 

   totalCount int;--总记录数

   pageSql varchar(2000); --查询某页结果的SQL语句

begin 

  select count(1) into totalCount from Account;  --查询总记录数

  totalPage := ceil( totalCount / perPageCount);   --算出总页数

  pageSql := 'select * from v_page u  

    where rn between '||(page-1)||'*'||perPageCount||'+1 and '||(page*perPageCount); 

   open pageResultSet for pageSql; 

end P_Pagination; 

 

 在PL/SQL中调用该存储过程

Sql代码

declare  

   totalaPage int;  --总页数

   pageResult  SYS_REFCURSOR; --存放结果的变量

   account v_page%rowtype; 

 begin 

    P_Pagination(1,2,totalaPage,pageResult); 

    dbms_output.put_line('总共'||totalaPage||'页'); 

    fetch pageResult into account; 

   while pageResult%found loop 

      dbms_output.put_line(account.cardid||','||account.name||','||account.money); 

      fetch pageResult into account; 

    end loop; 

   close pageResult; 

end;

 

使用JDBC调用的代码片段

Java代码

public static void main(String[] args) throws SQLException { 

    Connection conn = ConnectionManage.getConnection(); 

    CallableStatement cs = conn.prepareCall("call P_Pagination(?,?,?,?)"); 

    cs.setInt(1, 1); 

    cs.setInt(2, 3); 

    cs.registerOutParameter(3, Types.INTEGER); 

    cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR); 

    cs.execute(); 

    int totalPage = cs.getInt(3); 

    System.out.println("共有" + totalPage + "页"); 

    ResultSet rs = (ResultSet) cs.getObject(4); 

    while (rs.next()) { 

        System.out.println(rs.getString("cardid")+","+rs.getString("name") +"," + rs.getString("money")); 

    } 

}