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

CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;
           

Oracle會使用這個類型緩存少量的記錄作為pipelined函數調用時的輸出。我們建立一個簡單的pipelined函數。

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函數，我們可以測試一下。

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類型。

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類型

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整除的數字輸出出來。

再建立一個輸出時間到毫秒的函數，用于測試兩個函數的輸出特點。

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

第三步，測試兩個函數

測試普通函數如下：

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函數如下：

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記憶體的消耗又完全不一樣，這點更重要。