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;