Oracle 收集以流水线方式检索数据需要更多时间，即使查询也需要更少时间

askTom 2018-09-18

265

问题描述

嗨，先生，

面临时间问题。在动态游标类型中使用的查询，如 'open for select '采取550毫秒，但我们作为集合传递的输出作为流水线值。当时它需要更多的时间，如50 s。请给我们一些解决方案。

示例代码，如，

Function fn-name (a number, b varchar, c varchar)
Return records as pipelined 
Is
Begin
 Case when a = 1
  Then 
      Open c for 
             Select * from t1 where column = c;
When a = 2
     Then 
        Open c for
           Select * from t1 where column =c;
End case;

Fetch c into output collection variable;

Close c;
Return output collection variable;

End;
复制

已经创建了集合变量，并将其作为示例。

谢谢，
Manoj

专家解答

抱歉，我不能复制那个结果。我们需要看看你的测试用例。这是我的

SQL> @drop t

Y1                                                                                           Y2
-------------------------------------------------------------------------------------------- -------------------------
TABLE                                                                                        cascade constraints purge

1 row selected.


Table dropped.

SQL> @drop sample_list

Y1                                                                                           Y2
-------------------------------------------------------------------------------------------- -------------------------
TYPE

1 row selected.


Type dropped.

SQL> @drop sample_row

Y1                                                                                           Y2
-------------------------------------------------------------------------------------------- -------------------------
TYPE

1 row selected.


Type dropped.

SQL>
SQL> create or replace
  2  type  sample_row as object
  3    ( x int,
  4      y date,
  5      z varchar2(200)
  6     );
  7  /

Type created.

SQL>
SQL> create or replace
  2  type sample_list as table of sample_row
  3  /

Type created.

SQL>
SQL> create table t as select d.* from dba_objects d,
  2     ( select 1 from dual connect by level <= 10 );

Table created.

SQL> select count(*) from t;

  COUNT(*)
----------
    795110

1 row selected.

SQL>
SQL> create or replace
  2  function f1(p_owner varchar2) return sample_list is
  3    rc sys_refcursor;
  4    result sample_list := sample_list();
  5  begin
  6    open rc for
  7      'select sample_row(object_id,created,object_name) from t where owner = :1' using p_owner;
  8    fetch rc
  9    bulk collect into result;
 10    close rc;
 11    return result;
 12  end;
 13  /

Function created.

SQL>
SQL> create or replace
  2  function f2(p_owner varchar2) return sample_list pipelined is
  3    rc sys_refcursor;
  4    result sample_list := sample_list();
  5  begin
  6    open rc for
  7      'select sample_row(object_id,created,object_name) from t where owner = :1' using p_owner;
  8    fetch rc
  9    bulk collect into result;
 10    close rc;
 11    for i in 1 .. result.count
 12    loop
 13      pipe row ( result(i) );
 14    end loop;
 15    return;
 16  end;
 17  /

Function created.

SQL>
SQL> set timing on
SQL> select max(x), count(*) from table(f1('SYS'));

    MAX(X)   COUNT(*)
---------- ----------
    107230     519710

1 row selected.

Elapsed: 00:00:01.60
SQL> select max(x), count(*) from table(f2('SYS'));

    MAX(X)   COUNT(*)
---------- ----------
    107230     519710

1 row selected.

Elapsed: 00:00:00.80
SQL>
SQL>
SQL>
复制