oracle千萬級資料分頁存儲過程優化

随着資料量的增加，Oracle資料庫分頁存儲過程（使用rownum分頁）查詢性能越來越差，查詢時間也越來越長，于是優化勢在必行，結合使用者一般使用特點（一般看前幾頁的較多），于是以此為切入點優化原先的存儲過程，在WHERE條件中增加rownum<=pageindex*pageSize，減少首次過濾的資料量，調整後的存儲過程如下：

CREATE OR REPLACE PACKAGE DotNet is
  TYPE type_cur IS REF CURSOR; --定義遊标變量用于傳回記錄集
  PROCEDURE DotNetPagination(Pindex in number, --分頁索引
                                 Psql   in varchar2, --産生dataset的sql語句
                                 Psize  in number, --頁面大小
                                 v_cur  out type_cur --傳回目前頁資料記錄
                                 );
  procedure DotNetPageRecordsCount(Psqlcount in varchar2, --産生dataset的sql語句
                                       Prcount   out number --傳回記錄總數
                                       );
end DotNet; 
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;
           

 經測試，性能增加幾十倍以上

如SQL語句： select * from tb where date between to_date('2011-7-1','yyyy-mm-dd') and to_date('2011-8-1','yyyy-mm-dd')  查詢結果有100W記錄，原分頁存儲過程取某一頁的資料用時100秒以上，優化後的存儲過程時間可以縮小到零點幾秒