oracle千万级数据分页存储过程优化

随着数据量的增加，Oracle数据库分页存储过程（使用rownum分页）查询性能越来越差，查询时间也越来越长，于是优化势在必行，结合用户一般使用特点（一般看前几页的较多），于是以此为切入点优化原先的存储过程，在WHERE条件中增加rownum<=pageindex*pageSize，减少首次过滤的数据量，调整后的存储过程如下：

CREATE OR REPLACE PACKAGE DotNet is
  TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
  PROCEDURE DotNetPagination_New(Pindex in number, --分页索引
                                 Psql   in varchar2, --产生dataset的sql语句
                                 Psize  in number, --页面大小
                                 v_cur  out type_cur --返回当前页数据记录
                                 );
  procedure DotNetPageRecordsCount_New(Psqlcount in varchar2, --产生dataset的sql语句
                                       Prcount   out number --返回记录总数
                                       );
end DotNet_New;
CREATE OR REPLACE PACKAGE BODY DotNet is
  PROCEDURE DotNetPagination(Pindex in number,
                             Psql   in varchar2,
                             Psize  in number,
                             v_cur  out type_cur) AS
    v_sql    VARCHAR2(4000);
    v_count  number;
    v_Plow   number;
    v_Phei   number;
    v_Appsql varchar2(1000);
  Begin
    v_Phei   := Pindex * Psize + Psize;
    v_Plow   := v_Phei - Psize + 1;
    --优化的地方--------------
    v_Appsql := '';
    if (Pindex < 1000) then
      v_Appsql := ' and rownum <= ' || v_Phei;
    end if;   
    v_sql := 'select * from (' || Psql || v_Appsql ||
             ') where rn between ' || v_Plow || ' and ' || v_Phei;
    ----------------------------
    --原方法v_sql := 'select * from (' || Psql ||  ') where rn between ' || v_Plow || ' and ' || v_Phei;
    open v_cur for v_sql;
  End DotNetPagination;
  procedure DotNetPageRecordsCount(Psqlcount in varchar2,
                                   Prcount   out number) as
    v_sql     varchar2(4000);
    v_prcount number;
  begin
    v_sql := 'select count(*) from (' || Psqlcount || ')';
    execute immediate v_sql
      into v_prcount;
    Prcount := v_prcount;
  end DotNetPageRecordsCount;
end DotNet;