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

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;