Oracle的pipelined函数提升数据输出性能

前端之家收集整理的这篇文章主要介绍了Oracle的pipelined函数提升数据输出性能前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。


从Oracle 8开始,我们就可以从一个collection类型的数据集合中查询出数据,这个集合称之为“虚拟表“。它的方法是“SELECT FROM TABLE(CAST(plsql_function AS collection_type))”,据说该方法在处理大数据量时会有内存方面的限制。到了Oracle 9i之后,一个称为pipelined表函数的技术被推出来。他和普通的表函数很类似,但还有有一些显著的差别。
第一,pipelined函数处理的数据,是以管道的方式,或者说是流的方式从预先准备的小数组中展现给用户,而普通表函数将数据全部准备好再展现给用户
第二,pipelined函数可以并发,这意味着PLsql可以同一时间在多个进程上执行。
第三,pipelined函数可以很容易将存储过程转换成用bulk操作的行,有利于实现的复杂转换逻辑的sql

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1628397 )

了解pipelined函数的最佳方法是看一个简单的例子。对于任何一个pipelined函数,都必须有两点要求。
1、一个定义在模式中或者包中collection类型;
2、一个单独的PL/sql函数或一个包中的函数函数的返回类型后面必须加pipelined关键字;


在下面的例子中,我们将创建一个简单的pipelined函数输出若干行记录。首先需要一个collection类型,用于格式化输出

@H_403_21@
  • CREATEORREPLACETYPEnumber_nttASTABLEOFNUMBER;

  • Oracle会使用这个类型缓存少量的记录作为pipelined函数调用时的输出。我们创建一个简单的pipelined函数

    @H_403_21@
  • CREATEORREPLACEFUNCTIONrow_generator(rows_inINPLS_INTEGER)
  • RETURNnumber_nttPIPELINED
  • IS
  • BEGIN
  • FORiIN1..rows_inLOOP
  • PIPEROW(i);
  • ENDLOOP;
  • RETURN;
  • END;
  • 在这个sql中:
    函数定义部分的关键字pipelined是pipelined函数定义的关键,返回的类型必须是事先定义的collection类型,如这里是number_tt。
    函数主体部分的”PIPE ROW”是将一个单行记录写入到collection流中。记录中所有字段的类型必须和collection类型中所有字段匹配。
    函数主体部分的“return“的值是一个空值,而不是有任何符合collection类的值。
    这些就是pipelined函数定义时需要严格遵守的规则。


    现在已经创建好一个pipelined函数,我们可以测试一下。

    @H_403_21@
  • sql>select*fromTABLE(row_generator(10));
  • COLUMN_VALUE
  • ------------
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 10rowsselected

  • 将pipelined函数row_generator放到一个“TABLE”操作符中,虚拟成一个数据源,类似表或视图。这里虚拟表只有一个字段,名称“COLUMN_VALUE“是其默认值。更复杂的输出则需要将collection定义得更复杂些,使用到object或者record。


    我们通过一个例子比较一下pipelined函数或普通的表函数在返回collection时有何差异。


    第一步,创建普通的表函数,返回colletion类型。

    @H_403_21@
  • CREATEORREPLACEFUNCTIONtable_functionRETURNnumber_nttAS
  • ntnumber_ntt:=number_ntt();
  • BEGIN
  • FORiIN1..500000LOOP
  • if(mod(i,10000)=0)then
  • nt.EXTEND;
  • nt(nt.LAST):=i;
  • endif;
  • ENDLOOP;
  • RETURNnt;--<--returnwholecollection
  • ENDtable_function;

  • 第二步,创建pipelined函数,返回的也是collection类型

    @H_403_21@
  • CREATEORREPLACEFUNCTIONpipelined_functionRETURNnumber_ntt
  • PIPELINEDAS
  • BEGIN
  • FORiIN1..500000LOOP
  • if(mod(i,10000)=0)then
  • PIPEROW(i);--<--sendrowtoconsumer
  • endif;
  • ENDLOOP;
  • RETURN;
  • ENDpipelined_function;


  • 函数功能都是将能和1000整除的数字输出出来。
    再创建一个输出时间到毫秒的函数,用于测试两个函数输出特点。

    @H_403_21@
  • CREATEFUNCTIONget_timeRETURNTIMESTAMPIS
  • BEGIN
  • RETURNLOCALTIMESTAMP;
  • ENDget_time;
  • /

  • 第三步,测试两个函数


    测试普通函数如下:

    @H_403_21@
  • ALTERSESSIONSETNLS_TIMESTAMP_FORMAT='HH24:MI:SS.FF3';
  • SELECTget_time()AStsFROMDUAL;
  • SELECTcolumn_value,get_time()AStsFROMTABLE(table_function);
  • sql>
  • TS
  • --------------------------------------------------------------------------------
  • 15:27:26.031
  • COLUMN_VALUETS
  • --------------------------------------------------------------------------------------------
  • 10000015:27:26.218
  • 20000015:27:26.218
  • 30000015:27:26.218
  • 40000015:27:26.218
  • 50000015:27:26.218
  • sql>
  • 结果显示,所有记录都是同一时间输出

    测试pipelined函数如下:

    @H_403_21@
  • SELECTget_time()AStsFROMDUAL;
  • SELECTcolumn_value,get_time()AStsFROMTABLE(pipelined_function);
  • TS
  • --------------------------------------------------------------------------------
  • 15:27:26.265
  • COLUMN_VALUETS
  • --------------------------------------------------------------------------------------------
  • 10000015:27:26.312
  • 20000015:27:26.343
  • 30000015:27:26.390
  • 40000015:27:26.421
  • 50000015:27:26.453
  • 结果显示,所有记录都是逐次输出。 这点对于用户的UI太重要了。试想,如果执行一个查询,过了10秒钟才显示出所有的结果好,还是还是每秒都显示一些记录,知道10秒钟显示完毕好? 如果这个输出的结果集再放到到百万记录,两个函数对PGA内存的消耗又完全不一样,这点更重要。

    猜你在找的Oracle相关文章