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类型，用于格式化输出。

1. CREATEORREPLACETYPEnumber_nttASTABLEOFNUMBER;

CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;

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

1. CREATEORREPLACEFUNCTIONrow_generator(rows_inINPLS_INTEGER)
2. RETURNnumber_nttPIPELINED
3. IS
4. BEGIN
5. FORiIN1..rows_inLOOP
6. PIPEROW(i);
7. ENDLOOP;
8. RETURN;
9. END;

CREATE OR REPLACE FUNCTION row_generator(rows_in IN PLS_INTEGER)
  RETURN number_ntt  PIPELINED
IS
BEGIN
  FOR i IN 1 .. rows_in LOOP
    PIPE ROW(i);
  END LOOP;
  RETURN;
END;

在这个SQL中:
在函数定义部分的关键字pipelined是pipelined函数定义的关键，返回的类型必须是事先定义的collection类型，如这里是number_tt。
在函数主体部分的”PIPE ROW”是将一个单行记录写入到collection流中。记录中所有字段的类型必须和collection类型中所有字段匹配。
在函数主体部分的“return“的值是一个空值，而不是有任何符合collection类的值。
这些就是pipelined函数定义时需要严格遵守的规则。

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

1. SQL>select*fromTABLE(row_generator(10));
3. COLUMN_VALUE
4. ------------
5. 1
6. 2
7. 3
8. 4
9. 5
10. 6
11. 7
12. 8
13. 9
14. 10
16. 10rowsselected

SQL> select * from TABLE( row_generator(10) );
 
COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10
 
10 rows selected

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

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

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

1. CREATEORREPLACEFUNCTIONtable_functionRETURNnumber_nttAS
2. ntnumber_ntt:=number_ntt();
3. BEGIN
4. FORiIN1..500000LOOP
5. if(mod(i,10000)=0)then
6. nt.EXTEND;
7. nt(nt.LAST):=i;
8. endif;
10. ENDLOOP;
11. RETURNnt;--<--returnwholecollection
12. ENDtable_function;

CREATE OR REPLACE FUNCTION table_function RETURN number_ntt AS
  nt number_ntt := number_ntt();
BEGIN
  FOR i IN 1 .. 500000 LOOP
    if (mod(i,10000) = 0) then
      nt.EXTEND;
      nt(nt.LAST) := i;
    end if;
 
  END LOOP;
  RETURN nt; --<-- return whole collection
END table_function;

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

1. CREATEORREPLACEFUNCTIONpipelined_functionRETURNnumber_ntt
2. PIPELINEDAS
3. BEGIN
4. FORiIN1..500000LOOP
5. if(mod(i,10000)=0)then
6. PIPEROW(i);--<--sendrowtoconsumer
7. endif;
8. ENDLOOP;
9. RETURN;
10. ENDpipelined_function;

CREATE OR REPLACE FUNCTION pipelined_function RETURN number_ntt
  PIPELINED AS
BEGIN
  FOR i IN 1 .. 500000 LOOP
    if (mod(i,10000) = 0) then
      PIPE ROW(i); --<-- send row to consumer
    end if;
  END LOOP;
  RETURN;
END pipelined_function;

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

1. CREATEFUNCTIONget_timeRETURNTIMESTAMPIS
2. BEGIN
3. RETURNLOCALTIMESTAMP;
4. ENDget_time;
5. /

CREATE FUNCTION get_time RETURN TIMESTAMP IS
BEGIN
   RETURN LOCALTIMESTAMP;
END get_time;
/

第三步，测试两个函数

测试普通函数如下：

1. ALTERSESSIONSETNLS_TIMESTAMP_FORMAT='HH24:MI:SS.FF3';
2. SELECTget_time()AStsFROMDUAL;
3. SELECTcolumn_value,get_time()AStsFROMTABLE(table_function);
4. SQL>
6. TS
7. --------------------------------------------------------------------------------
8. 15:27:26.031
10. COLUMN_VALUETS
11. --------------------------------------------------------------------------------------------
12. 10000015:27:26.218
13. 20000015:27:26.218
14. 30000015:27:26.218
15. 40000015:27:26.218
16. 50000015:27:26.218
18. SQL>

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'HH24:MI:SS.FF3';
SELECT get_time() AS ts FROM DUAL;
SELECT column_value,get_time() AS ts FROM TABLE(table_function);
SQL>
 
TS
--------------------------------------------------------------------------------
15:27:26.031
 
COLUMN_VALUE TS
------------ --------------------------------------------------------------------------------
      100000 15:27:26.218
      200000 15:27:26.218
      300000 15:27:26.218
      400000 15:27:26.218
      500000 15:27:26.218
 
SQL>

结果显示，所有记录都是同一时间输出。

测试pipelined函数如下：

1. SELECTget_time()AStsFROMDUAL;
2. SELECTcolumn_value,get_time()AStsFROMTABLE(pipelined_function);
3. TS
4. --------------------------------------------------------------------------------
5. 15:27:26.265
7. COLUMN_VALUETS
8. --------------------------------------------------------------------------------------------
9. 10000015:27:26.312
10. 20000015:27:26.343
11. 30000015:27:26.390
12. 40000015:27:26.421
13. 50000015:27:26.453

SELECT get_time() AS ts FROM DUAL;
SELECT column_value,get_time() AS ts FROM TABLE(pipelined_function);
TS
--------------------------------------------------------------------------------
15:27:26.265
 
COLUMN_VALUE TS
------------ --------------------------------------------------------------------------------
      100000 15:27:26.312
      200000 15:27:26.343
      300000 15:27:26.390
      400000 15:27:26.421
      500000 15:27:26.453

结果显示，所有记录都是逐次输出。 这点对于用户的UI太重要了。试想，如果执行一个查询，过了10秒钟才显示出所有的结果好，还是还是每秒都显示一些记录，知道10秒钟显示完毕好？ 如果这个输出的结果集再放到到百万记录，两个函数对PGA内存的消耗又完全不一样，这点更重要。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!