PL/SQL性能分页游标(具有筛选和固定结果大小)

Question

我有一个pl/sql游标，它从定义的用于分页查询的rowid (由参数给定)开始。

游标检索到的行将按特殊规则进行筛选-因此，结果行不会立即返回，而是收集在表类型变量中。

在一个循环中，我从游标中获取更多的数据，直到将100个“行”放入表类型变量。这个结果是从函数传回的。

一切正常，但初始sql查询(open cursor)需要很长时间。如果我限制sql语句中的行数( "WHERE rownum <= (100 +p_startfrom)“中的注释)，查询速度会快得多。但是我不知道我需要通过循环和过滤来获取多少数据。

有没有一种方法可以通过不添加固定的"rownum <=“where子句来使函数更快？

  function get_metadata(p_toquery in number, p_startfrom in number) return t_Table_metadata
  is
    v_result_tab t_Table_metadata;
    v_return_tab t_Table_metadata := t_Table_metadata();
    
    c_meta SYS_REFCURSOR;
    
    v_size_per_fetch integer := 100;    
  begin
    open c_meta for 
      'SELECT t_type_metadata(....)
      FROM (
        SELECT t.*, rownum r
        FROM (
          select ...
          from   ...
          where  attr1 =  '|| p_toquery||'
          order  by create_date desc
        ) t
      -- with this where-Clause much faster!! WHERE rownum <= (100 + p_startfrom)
      ) f
      WHERE r >= '|| p_startfrom;

    loop
      fetch c_meta bulk collect into v_result_tab limit v_size_per_fetch;

        for i in 1..v_result_tab.count loop

          v_check_row_result := check_row(....);

          if (v_check_row_result = 1 and v_return_tab.count < 100) then
            v_return_tab.extend;
            v_return_tab(v_return_tab.count) := v_result_tab(i);
          end if;

        end loop;

      exit when v_result_tab.count = 0 or v_return_tab.count >= 100;
    end loop;
    close c_meta;
    
    return v_return_tab;
  end;

Answer 1

使用提示FIRST_ROWS请求Oracle优化查询，以便快速返回少量行。您仍然需要猜测PL/SQL循环所需的行数，但是大概的猜测，比如1000，可能已经足够好了。

SELECT /*+ first_rows(1000) */ t_type_metadata(....)

正如其他人所评论的那样，如果您可以将所有条件都放入一条SQL语句中，那将是最理想的。使用一条SQL语句，添加分页就可以像下面这样简单：select ... offset 100 rows fetch next 100 rows only;