[交流]自己写的oracle分页存储过程，效率不太满意，有高手能优化下吗？听说标题比较长，效果明显，快来接分啊。。。

Question

我写的这个分页过程，效率一般。
测试规模68万数据
不开查询条件，平均执行时间要6秒
查询条件全开，平均执行时间要15秒以上有高手能帮忙优化一下吗？查询条件全开的情况下，平均执行时间在8秒以下即为优化成功！
以下是源代码：create or replace package package_page
/**
 *创建分页辅助包，用于定义游标类型作为过程一个出口参数的类型
 */
is
  type RETCURSOR is REF CURSOR;
end;
create or replace procedure proc_page
/**
 *分页存储过程
 *输入：page_id(目标页号)、
        page_size(每页记录数)、
        field_info(目标字段列表：可多个字段，以","号分隔；可为空串，空串取默认值'*'；可为'*')、
        table_info(目标表或视图，只支持单表或视图)、
        where_info(条件字符串)、
        order_field(排序字段)、
        order_flag(当order_field为空时,会自动忽略此项;0升序、其他降序)
 
 *输出：proc_flag
            (存储过程执行结果标志：
                 0、异常退出(一般应为sql语句执行异常)
                 1、每页记录数小于或等于0
                 2、没有提供表或视图
                 3、目标页面不在范围内
                 4、成功
            )、
        cursor_result(结果集游标)、
        record_count(总记录数)、
        page_count(总页数)
 *使用示例：proc_page(1,20,'name','mytable','name like ''g%''','age',0,proc_flag,cursor_result,record_count,page_count);
 */
(
  page_id in number,
  page_size in number,
  field_info in varchar2,
  table_info in varchar2,
  where_info in varchar2,
  order_field in varchar2,
  order_flag in number,  proc_flag out number,
  cursor_result out package_page.RETCURSOR,
  record_count out number,
  page_count out number
)
is
  proc_field_info varchar2(512);
  proc_table_info varchar2(512);
  proc_where_info varchar2(2048);
  proc_order_field varchar2(512);
  proc_order_sort varchar2(128);
  
  tmp_cur package_page.RETCURSOR;
  tmp_length number;
begin
  if page_size<=0 then
    proc_flag:=1;
    return;
  end if;  proc_field_info:=trim(field_info);
  select decode(proc_field_info,'',0,proc_field_info,length(proc_field_info)) into tmp_length from dual;
  if tmp_length=0 then
    proc_field_info:='*';
  end if;
  
  proc_table_info:=trim(table_info);
  select decode(proc_table_info,'',0,proc_table_info,length(proc_table_info)) into tmp_length from dual;
  if tmp_length=0 then
    proc_flag:=2;
    return;  --没有提供表或视图
  end if;  proc_where_info:=trim(where_info);
  select decode(proc_where_info,'',0,proc_where_info,length(proc_where_info)) into tmp_length from dual;
  if tmp_length>0 then
    proc_where_info:='where '||proc_where_info;
  end if;
  
  proc_order_field:=trim(order_field);
  select decode(proc_order_field,'',0,proc_order_field,length(proc_order_field)) into tmp_length from dual;
  if tmp_length>0 then
    proc_order_field:='order by '||proc_order_field;
    if order_flag=0 then
      proc_order_sort:='asc';
    else
      proc_order_sort:='desc';
    end if;
  else
    proc_order_sort:='';
  end if;
      
  open tmp_cur for
      'select count(*) from '||proc_table_info||' '||proc_where_info;
  fetch tmp_cur into record_count;
  close tmp_cur;  if(mod(record_count,page_size)=0) then
    page_count:=record_count/page_size;
  else
    page_count:=record_count/page_size+1;
  end if;
  
  if(page_id<=0 or page_id>page_count) then
    proc_flag:=3;
    return;  --目标页面不在范围内
  end if;  open cursor_result for
            'select '||proc_field_info||
            ' from '|| 
            '('||
            'select rownum row_id,a.* from '||proc_table_info||' a '||proc_where_info||' '||proc_order_field||' '||proc_order_sort||
            ') '||
            'where row_id>('||page_id||'-1)*'||page_size||' and row_id<=('||page_id||'-1)*'||page_size||'+'||page_size;
  proc_flag:=4;
  return;
exception
  when others then
    proc_flag:=0;
    return;  
end proc_page;
个人估计是过多使用下面这条语句所致，不知有什么替代方法吗？select decode(proc_field_info,'',0,proc_field_info,length(proc_field_info)) into tmp_length from dual;

Answer 1

你最后的sql分页的时候先写and row_id<=('||page_id||'-1)*'||page_size||'+'||page_size;
然后外面来层，再写row_id>('||page_id||'-1)*'||page_size||'
大概写法
'select * from (
'select '||proc_field_info||
            ' from '|| 
            '('||
            'select rownum row_id,a.* from '||proc_table_info||' a '||proc_where_info||' '||proc_order_field||' '||proc_order_sort||
            ') '||
            'where  row_id<=('||page_id||'-1)*'||page_size||'+'||page_size
) where row_id>('||page_id||'-1)*'||page_size||';

Answer 2

HelloWorld_001 的方案有道理，但我试了一下，效果差不多。
不过我发现了新的问题：
我用如下PL/SQL程序块测试时，发现效率低下的不是存储过程的执行，而是fetch结果集的时候
这没道理啊，盼星期一的时候有人能指教下declare
  flag number(1);
  type REF_CURSOR is REF CURSOR;
  cur REF_CURSOR;
  record_count number(12);
  page_count number(8);
  
  id varchar2(50);
  svc varchar2(50);
begin
  dbms_output.put_line('procedure begin:'||to_char(sysdate,'YYYYMMDDHH24MISS'));
  proc_page(1,20,'id,svc','userinfo','code like ''B%''','id',0,flag,cur,record_count,page_count);
  dbms_output.put_line('procedure end:'||to_char(sysdate,'YYYYMMDDHH24MISS'));
  dbms_output.put_line('FLAG='||flag||' RECORD-COUNT='||record_count||' PAGE-COUNT='||page_count);
  dbms_output.put_line('fetch begin:'||to_char(sysdate,'YYYYMMDDHH24MISS'));
  loop
  fetch cur into id,svc;
  exit when cur%notfound;
  dbms_output.put_line('ID='||id||'  SVC='||svc);
  end loop;
  dbms_output.put_line('fetch end:'||to_char(sysdate,'YYYYMMDDHH24MISS'));
  close cur;
end;结果如下：

procedure begin:20080427082758
procedure end:20080427082759
FLAG=4 RECORD-COUNT=658482 PAGE-COUNT=32925
fetch begin:20080427082759
/*这里是显示出来的记录集*/
fetch end:20080427082811

Answer 3

建议换成select top order by这种方式看看

Answer 4

方案1
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 20 id
         FROM TestTable
         ORDER BY id))
ORDER BY IDSELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 页大小*页数 id
         FROM 表
         ORDER BY id))
ORDER BY ID方案2
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 页大小*页数 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID没有调试环境，仅供参考

Answer 5

伴水 清洁工这个是oracle，没有top的
你的是sql server的。我认为楼主写的不错了，更好的修改不知道了

Answer 6

找个接分的借口不容易，再接一个Select Column1,Column2,Column3....   
      From (Select Column1,Column2,Column3.... From TableName Order by Column1,Column2,Column3.... Desc)   
      Where RowNum < n;   

Answer 7

因为要在子查询中构造row_id字段，所以没办法呵呵，这是ORACLE的存储过程看来真的没办法了
谢谢以上诸位，看来我要想好怎么去侃晕老板了，呵呵

Answer 8

oracle 8i以后开始支持“RowNum”（列数）字段，试一试先。

Answer 9

 select decode(proc_field_info,'',0,proc_field_info,length(proc_field_info)) into tmp_length from dual;
---------->
 select decode(proc_field_info,'',0,1) into tmp_length from dual;

Answer 10

.........
page_id in number,
  page_size=1 in number,
  field_info='stuName' in varchar2,
  table_info='stuInfo' in varchar2,
  where_info='' in varchar2,
  order_field='' in varchar2,
  order_flag in number,  proc_flag out number,
  cursor_result out package_page.RETCURSOR,
  record_count out number,
  page_count out number............
直接赋值来代替 
select decode(proc_field_info,'',0,proc_field_info,length(proc_field_info)) into tmp_length from dual;