Oracle Pipelined Table Functions

table型函数用来返回类似表结构的集合，常规的table型函数需要先填充完集合才能返回，如果集合对象太大会消耗大量内存；pipelined的table型函数能够在边填充时边返回，从而避免占用过多内存。

Table Functions

Table functions are used to return PL/SQL collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations. Regular table functions require named row and table types to be created as database objects.

-- Create the types to support the table function. DROP TYPE t_tf_tab; DROP TYPE t_tf_row; CREATE TYPE t_tf_row AS OBJECT ( id NUMBER, description VARCHAR2(50) ); / CREATE TYPE t_tf_tab IS TABLE OF t_tf_row; / -- Build the table function itself. CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS l_tab t_tf_tab := t_tf_tab(); BEGIN FOR i IN 1 .. p_rows LOOP l_tab.extend; l_tab(l_tab.last) := t_tf_row(i, 'Description for ' || i); END LOOP; RETURN l_tab; END; / -- Test it. SELECT * FROM TABLE(get_tab_tf(10)) ORDER BY id DESC; ID DESCRIPTION ---------- -------------------------------------------------- 10 Description for 10 9 Description for 9 8 Description for 8 7 Description for 7 6 Description for 6 5 Description for 5 4 Description for 4 3 Description for 3 2 Description for 2 1 Description for 1 10 rows selected. SQL>

Notice the above output is in reverse order because the query includes a descending order by clause.

Pipelined Table Functions

Pipelining negates the need to build huge collections by piping rows out of the function as they are created, saving memory and allowing subsequent processing to start before all the rows are generated.

Pipelined table functions include the PIPELINED clause and use the PIPE ROW call to push rows out of the function as soon as they are created, rather than building up a table collection. Notice the empty RETURN call, since there is no collection to return from the function.

-- Build a pipelined table function. CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS BEGIN FOR i IN 1 .. p_rows LOOP PIPE ROW(t_tf_row(i, 'Description for ' || i)); END LOOP; RETURN; END; / -- Test it. SELECT * FROM TABLE(get_tab_ptf(10)) ORDER BY id DESC; ID DESCRIPTION ---------- -------------------------------------------------- 10 Description for 10 9 Description for 9 8 Description for 8 7 Description for 7 6 Description for 6 5 Description for 5 4 Description for 4 3 Description for 3 2 Description for 2 1 Description for 1 10 rows selected. SQL>

Once you start working with large warehousing ETL operations the performance improvements can be massive, allowing data loads from external tables via table functions directly into the warehouse tables, rather than loading via a staging area.

FROM & MORE:

http://www.oracle-base.com/articles/misc/pipelined-table-functions.php