Oracle Pipelined Table Functions 与 性能优化 说明

一.Pipelined table functions

官网说明如下：

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/tuning.htm#BCGICBDF

Chainingpipelined table functions is an efficient way to perform multipletransformations on data.

Note:

You cannot run a pipelined table function over a databaselink. The reason is that the return type of a pipelined table functionis a SQL user-defined type, which can be used only in a single database(as explained inOracleDatabase Object-Relational Developer's Guide). Although the return type ofa pipelined table function might appear to be a PL/SQL type, the databaseactually converts that PL/SQL type to a corresponding SQL user-defined type.

1.1 Overview of Table Functions

A table function is a user-definedPL/SQL function that returns a collection of rows (a nestedtable or varray). You can select from thiscollection as if it were a database table by invoking the table function insidethe TABLE clause in a SELECT statement. For example:

 SELECT * FROM TABLE(table_function_name(parameter_list))

A table function can take a collection of rows as input(that is, it can have an input parameter that is a nested table, varray, orcursor variable). Therefore, output from table function tf1 can be input totable function tf2, and output from tf2 can be input to table function tf3, andso on.

To improve theperformance of a table function, you can:

（1）Enable the function for parallel execution, with the PARALLEL_ENABLE option. Functions enabled for parallelexecution can run concurrently.

（2）Stream the function results directly to the next process, withOracle Streams.

Streamingeliminates intermediate staging between processes. For information about OracleStreams, seeOracleStreams Concepts and Administration.

（3）Pipeline the function results, with the PIPELINEDoption.

A pipelined table function returns a row to its invoker immediatelyafter processing that row and continues to process rows. Response time improves because the entire collection need not beconstructed and returned to the server before the query can return a singleresult row. (Also, the function needs lessmemory, because the object cache need not materialize the entirecollection.)

-- pipelined table function 立即返回row 记录，而不同等待这个collection，从而减少constructed的时间，并且占用较少的内存。

Caution:

A pipelined table function always references the currentstate of the data. If the data in the collection changes after thecursor opens for the collection, then the cursor reflects the changes. PL/SQL variables are private to a session and are nottransactional. Therefore, read consistency, well known for itsapplicability to table data, does not apply to PL/SQL collectionvariables.

1.2 CreatingPipelined Table Functions

Apipelined table function must be either a standalone stored function or apackage function.

1.2.1 PIPELINED Option (Required)

Fora standalone stored function, specify the PIPELINEDoption in the CREATE FUNCTION statement (for syntax, see"CREATEFUNCTION Statement"). For a package function, specify the PIPELINEDoption in both the function declaration and function definition (for syntax,see"FunctionDeclaration and Definition").

1.2.2 PARALLEL_ENABLE Option(Recommended)

Toimprove its performance, enable the pipelined table function for parallel executionby specifying the PARALLEL_ENABLE option.

1.2.3 AUTONOMOUS_TRANSACTION Pragma

Ifthe pipelined table function runs DML statements, thenmake it autonomous, with the AUTONOMOUS_TRANSACTIONpragma (described in"AUTONOMOUS_TRANSACTIONPragma"). Then, during parallel execution, each instance of thefunction creates an independent transaction.

1.2.4 DETERMINISTIC Option(Recommended)

Multipleinvocations of a pipelined table function, in either the same query or separatequeries, cause multiple executions of the underlying implementation. If thefunction is deterministic, specify the DETERMINISTICoption, described in"DETERMINISTIC".

1.2.5 Parameters

Typically,a pipelined table function has one or more cursor variable parameters. Forinformation about cursor variables as function parameters, see"CursorVariables as Subprogram Parameters".

See Also:

·"CursorVariables"for general information about cursor variables

·"SubprogramParameters"for general information about subprogram parameters

1.2.6 RETURN Data Type

Thedata type of the value that a pipelined table function returns must be acollection type defined either at schema level or inside a package (therefore,it cannot be an associative array type). The elements ofthe collection type must be SQL data types, not data types supported only byPL/SQL (such as PLS_INTEGER and BOOLEAN). For information about collectiontypes, see"CollectionTypes". For information about SQL data types, seeOracleDatabase SQL Language Reference.

Youcan use SQL data types ANYTYPE, ANYDATA,and ANYDATASET to dynamically encapsulate and accesstype descriptions, data instances, and sets of data instances of any other SQLtype, including object and collection types. You can also use these types tocreate unnamed types, including anonymous collection types. For informationabout these types, seeOracleDatabase PL/SQL Packages and Types Reference.

1.2.7 PIPE ROW Statement

Insidea pipelined table function, use the PIPE ROW statementto return a collection element to the invoker without returning control to theinvoker. See"PIPEROW Statement"for its syntax and semantics.

1.2.8 RETURN Statement

Asin every function, every execution path in a pipelined table function must leadto a RETURN statement, which returns control to the invoker. However, in apipelined table function, a RETURN statement need not return a value to theinvoker. See"RETURNStatement"for its syntax and semantics.

Example

Example12-29creates a package that includes a pipelined table function, f1, andthen selects from the collection of rows that f1 returns.

Example 12-29 Creating and Invoking Pipelined Table Function

CREATE OR REPLACE PACKAGE pkg1 AS

 TYPE numset_t IS TABLE OF NUMBER;

 FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;

END pkg1;

/

CREATE PACKAGE BODY pkg1 AS

 -- FUNCTION f1 returns a collection of elements (1,2,3,... x)

 FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS

 BEGIN

 FOR i IN 1..x LOOP

 PIPE ROW(i);

 END LOOP;

 RETURN;

 END f1;

END pkg1;

/

SELECT * FROM TABLE(pkg1.f1(5));

Result:

COLUMN_VALUE

------------

 1

 2

 3

 4

 5

5 rows selected.

1.3 PipelinedTable Functions as Transformation Functions

Apipelined table function with a cursor variable parameter can serve as atransformation function. Using the cursor variable, thefunction fetches an input row. Using the PIPE ROWstatement, the function pipes the transformed row or rows to theinvoker. If the FETCH and PIPE ROW statements areinside a LOOP statement, the function can transform multiple input rows.

InExample12-30, the pipelined table function transforms each selected row of the employeestable to two nested table rows, which it pipes to the SELECT statement thatinvokes it. The actual parameter that corresponds tothe formal cursor variable parameter is a CURSOR expression; forinformation about these, see"PassingCURSOR Expressions to Pipelined Table Functions".

Example 12-30 Pipelined Table Function Transforms Each Row toTwo Rows

CREATE OR REPLACE PACKAGE refcur_pkg IS

 TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE;

 TYPE outrec_typ IS RECORD (

 var_num NUMBER(6),

 var_char1 VARCHAR2(30),

 var_char2 VARCHAR2(30)

 );

 TYPE outrecset IS TABLE OF outrec_typ;

 FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED;

END refcur_pkg;

/

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS

 FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED IS

 out_rec outrec_typ;

 in_rec p%ROWTYPE;

 BEGIN

 LOOP

 FETCH p INTO in_rec; -- input row

 EXIT WHEN p%NOTFOUND;

 out_rec.var_num := in_rec.employee_id;

 out_rec.var_char1 := in_rec.first_name;

 out_rec.var_char2 := in_rec.last_name;

 PIPE ROW(out_rec); -- first transformed output row

 out_rec.var_char1 := in_rec.email;

 out_rec.var_char2 := in_rec.phone_number;

 PIPE ROW(out_rec); -- second transformed output row

 END LOOP;

 CLOSE p;

 RETURN;

 END f_trans;

END refcur_pkg;

/

SELECT * FROM TABLE (

 refcur_pkg.f_trans (

 CURSOR (SELECT * FROM employees WHERE department_id = 60)

 )

);

Result:

VAR_NUM VAR_CHAR1 VAR_CHAR2

---------- ------------------------------

103 Alexander Hunold

 103 AHUNOLD 590.423.4567

 104 Bruce Ernst

 104 BERNST 590.423.4568

 105 David Austin

 105 DAUSTIN 590.423.4569

 106 Valli Pataballa

 106 VPATABAL 590.423.4560

 107 Diana Lorentz

 107 DLORENTZ 590.423.5567

10 rows selected.

1.4 ChainingPipelined Table Functions

Tochain pipelined table functions tf1 and tf2 is to make the output of tf1 theinput of tf2. For example:

 SELECT * FROM TABLE(tf2(CURSOR(SELECT * FROM TABLE(tf1()))));

Therows that tf1 pipes out must be compatible actual parameters for the formalinput parameters of tf2.

Ifchained pipelined table functions are enabled for parallel execution, then eachfunction runs in a different process (or set of processes).

See Also:

"PassingCURSOR Expressions to Pipelined Table Functions"

1.5 Fetching fromResults of Pipelined Table Functions

Youcan associate a named cursor witha query that invokes a pipelined table function. Such a cursor has no specialfetch semantics, and such a cursor variable has no special assignmentsemantics.

However,the SQL optimizer does not optimizeacross PL/SQL statements. Therefore, inExample12-31, the first PL/SQL statement is slower thanthe second—despite the overhead of running two SQL statements in thesecond PL/SQL statement, and even if function results are piped between the twoSQL statements in the first PL/SQL statement.

InExample12-31, assume that f and g are pipelined table functions, and that eachfunction accepts a cursor variable parameter. The first PL/SQL statementassociates cursor variable r with a query that invokes f, and then passes r to g.The second PL/SQL statement passes CURSOR expressions to both f and g.

Example12-31 Fetching from Results of Pipelined Table Functions

DECLARE

 r SYS_REFCURSOR;

 ...

 -- First PL/SQL statement (slower):

BEGIN

 OPEN r FOR SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab)));

 SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r));

 -- NOTE: When g completes, it closes r.

END;

-- Second PL/SQL statement (faster):

SELECT * FROM TABLE(g(CURSOR(SELECT * FROM

 TABLE(f(CURSOR(SELECT * FROM tab))))));

/

See Also:

"CursorVariables as Subprogram Parameters"

1.6 Passing CURSORExpressions to Pipelined Table Functions

AsExample12-31shows, the actual parameter for the cursor variable parameter of apipelined table function can be either a cursor variable or a CURSOR expression, and the latter ismore efficient.

Note:

Whena SQL SELECT statement passes a CURSOR expression to a function, the referencedcursor opens when the function begins to run and closes when the functioncompletes.

See Also:

"CURSORExpressions"for general information about CURSOR expressions

Example12-32creates a package that includes a pipelined table function with twocursor variable parameters and then invokes the function in a SELECT statement,using CURSOR expressions for actual parameters.

Example 12-32 Pipelined Table Function with Two CursorVariable Parameters

CREATE OR REPLACE PACKAGE refcur_pkg IS

 TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;

 TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;

 TYPE outrec_typ IS RECORD (

 var_num NUMBER(6),

 var_char1 VARCHAR2(30),

 var_char2 VARCHAR2(30)

 );

 TYPE outrecset IS TABLE OF outrec_typ;

 FUNCTION g_trans (p1 refcur_t1, p2 refcur_t2) RETURN outrecset PIPELINED;

END refcur_pkg;

/

CREATE PACKAGE BODY refcur_pkg IS

 FUNCTION g_trans (

 p1 refcur_t1,

 p2 refcur_t2

 ) RETURN outrecset PIPELINED

 IS

 out_rec outrec_typ;

 in_rec1 p1%ROWTYPE;

 in_rec2 p2%ROWTYPE;

 BEGIN

 LOOP

 FETCH p2 INTO in_rec2;

 EXIT WHEN p2%NOTFOUND;

 END LOOP;

 CLOSE p2;

 LOOP

 FETCH p1 INTO in_rec1;

 EXIT WHEN p1%NOTFOUND;

 -- first row

 out_rec.var_num := in_rec1.employee_id;

 out_rec.var_char1 := in_rec1.first_name;

 out_rec.var_char2 := in_rec1.last_name;

 PIPE ROW(out_rec);

 -- second row

 out_rec.var_num := in_rec2.department_id;

 out_rec.var_char1 := in_rec2.department_name;

 out_rec.var_char2 := TO_CHAR(in_rec2.location_id);

 PIPE ROW(out_rec);

 END LOOP;

 CLOSE p1;

 RETURN;

 END g_trans;

END refcur_pkg;

/

SELECT * FROM TABLE (

 refcur_pkg.g_trans (

 CURSOR (SELECT * FROM employees WHERE department_id = 60),

 CURSOR (SELECT * FROM departments WHERE department_id = 60)

 )

);

Result:

VAR_NUM VAR_CHAR1 VAR_CHAR2

---------- ------------------------------ ------------------------------

 103 Alexander Hunold

 60 IT 1400

 104 Bruce Ernst

 60 IT 1400

 105 David Austin

 60 IT 1400

106 Valli Pataballa

 60 IT 1400

 107 Diana Lorentz

 60 IT 1400

10 rows selected.

Example12-33uses a pipelined table function as an aggregate function, which takes a set of input rows andreturns a single result. The SELECT statement selects the function result. (Forinformation about the pseudocolumn COLUMN_VALUE, seeOracleDatabase SQL Language Reference.)

Example 12-33 Pipelined Table Function as Aggregate Function

DROP TABLE gradereport;

CREATE TABLE gradereport (

 student VARCHAR2(30),

 subject VARCHAR2(30),

 weight NUMBER,

 grade NUMBER

);

INSERT INTO gradereport (student, subject, weight, grade)

VALUES ('Mark', 'Physics', 4, 4);

INSERT INTO gradereport (student, subject, weight, grade)

VALUES ('Mark','Chemistry', 4, 3);

INSERT INTO gradereport (student, subject, weight, grade)

VALUES ('Mark','Maths', 3, 3);

INSERT INTO gradereport (student, subject, weight, grade)

VALUES ('Mark','Economics', 3, 4);

CREATE PACKAGE pkg_gpa IS

 TYPE gpa IS TABLE OF NUMBER;

 FUNCTION weighted_average(input_values SYS_REFCURSOR)

 RETURN gpa PIPELINED;

END pkg_gpa;

/

CREATE PACKAGE BODY pkg_gpa IS

 FUNCTION weighted_average (input_values SYS_REFCURSOR)

 RETURN gpa PIPELINED

 IS

 grade NUMBER;

 total NUMBER := 0;

 total_weight NUMBER := 0;

 weight NUMBER := 0;

 BEGIN

 LOOP

 FETCH input_values INTO weight, grade;

 EXIT WHEN input_values%NOTFOUND;

 total_weight := total_weight + weight; -- Accumulate weighted average

 total := total + grade*weight;

 END LOOP;

 PIPE ROW (total / total_weight);

 RETURN; -- returns single result

 END weighted_average;

END pkg_gpa;

/

SELECT w.column_value "weighted result" FROM TABLE (

 pkg_gpa.weighted_average (

 CURSOR (SELECT weight, grade FROM gradereport)

 )

) w;

Result:

weighted result

---------------

 3.5

1 row selected.

1.7 DML Statementson Pipelined Table Function Results

The "table"that a pipelined table function returns cannot be thetarget table of a DELETE, INSERT, or UPDATE statement. However, you cancreate a view of such a table and create INSTEAD OFtriggers on the view. For information about INSTEAD OF triggers,see"INSTEADOF Triggers".

See Also:

OracleDatabase SQL Language Referencefor information about the CREATE VIEWstatement

1.8 NO_DATA_NEEDEDException

You must understand the predefinedexception NO_DATA_NEEDED in two cases:

（1）You include an OTHERS exception handler in ablock that includes a PIPE ROW statement

（2）Your code that feeds a PIPE ROW statement must be followed by aclean-up procedure。

Typically,the clean-up procedure releases resources that the code no longer needs.

Whenthe invoker of a pipelined table function needs no more rows from the function,the PIPE ROW statement raises NO_DATA_NEEDED.

If the pipelined table function does not handle NO_DATA_NEEDED,as inExample12-34, then the function invocation terminates but the invoking statementdoes not terminate.

If the pipelined table function handles NO_DATA_NEEDED,its exception handler can release the resources that it no longer needs, as inExample12-35.

InExample 12-34, the pipelined table functionpipe_rows does not handle the NO_DATA_NEEDED exception.The SELECT statement that invokes pipe_rows needs only four rows.Therefore, during the fifth invocation of pipe_rows, the PIPE ROW statementraises the exception NO_DATA_NEEDED. The fifth invocation of pipe_rowsterminates, but the SELECT statement does not terminate.

Example12-34 Pipelined Table Function Does Not Handle NO_DATA_NEEDED

CREATE TYPE t IS TABLE OF NUMBER

/

CREATE OR REPLACE FUNCTION pipe_rows RETURN t PIPELINED IS

 n NUMBER := 0;

BEGIN

 LOOP

 n := n + 1;

 PIPE ROW (n);

 END LOOP;

END pipe_rows;

/

SELECT COLUMN_VALUE

 FROM TABLE(pipe_rows())

 WHERE ROWNUM < 5

/

Result:

COLUMN_VALUE

------------

 1

 2

 3

 4

4 rows selected.

Ifthe exception-handling part of a block that includes a PIPE ROW statementincludes an OTHERS exception handler to handle unexpected exceptions, then itmust also include an exception handler for the expected NO_DATA_NEEDEDexception. Otherwise, the OTHERS exception handler handles the NO_DATA_NEEDEDexception, treating it as an unexpected error. The following exception handlerreraises the NO_DATA_NEEDED exception, instead of treating it as airrecoverable error:

EXCEPTION

 WHEN NO_DATA_NEEDED THEN

 RAISE;

 WHEN OTHERS THEN

 -- (Put error-logging code here)

 RAISE_APPLICATION_ERROR(-20000, 'Fatal error.');

END;

InExample12-35, assume that the package External_Source contains these public items:

（1）Procedure Init, which allocates andinitializes the resources that Next_Row needs

（2）Function Next_Row, which returnssome data from a specific external source and raises the user-defined exceptionDone (which is also a public item in the package) when the external source hasno more data

（3）Procedure Clean_Up, which releasesthe resources that Init allocated

Thepipelined table function get_external_source_data pipes rows from the externalsource by invoking External_Source.Next_Row until either:

（1）The external source has no more rows.

Inthis case, the External_Source.Next_Row function raises the user-definedexception External_Source.Done.

（2）get_external_source_data needs no more rows.

Inthis case, the PIPE ROW statement in get_external_source_data raises the NO_DATA_NEEDEDexception.

Ineither case, an exception handler in block b in get_external_source_datainvokes External_Source.Clean_Up, which releases the resources that Next_Rowwas using.

Example12-35 Pipelined Table Function Handles NO_DATA_NEEDED

CREATE OR REPLACE FUNCTION get_external_source_data

 RETURN t AUTHID DEFINER PIPELINED IS

BEGIN

 External_Source.Init(); -- Initialize.

 <<b>> BEGIN

 LOOP -- Pipe rows from external source.

 PIPE ROW (External_Source.Next_Row());

 END LOOP;

 EXCEPTION

 WHEN External_Source.Done THEN -- When no more rows are available,

 External_Source.Clean_Up(); -- clean up.

 WHEN NO_DATA_NEEDED THEN -- When no more rows are needed,

 External_Source.Clean_Up(); -- clean up.

 RAISE NO_DATA_NEEDED; -- Optional, equivalent to RETURN.

 END b;

END get_external_source_data;

/

小结：

You cannot run a pipelined table function over a databaselink. The reason is that the return type of a pipelinedtable function is a SQL user-defined type, which can be used only in a singledatabase

A pipelined table function returns a row to itsinvoker immediately after processing that rowand continues to process rows. Response time improvesbecause the entire collection need not be constructed and returned to the serverbefore the query can return a single result row. (Also, the function needs less memory, because the objectcache need not materialize the entire collection.)

The"table" that a pipelined table functionreturns cannot be the target table of a DELETE, INSERT, or UPDATE statement.However, you can create a view of such a table and create INSTEAD OF triggerson the view.

pipelined表函数的基本思路：在函数执行过程中就返回记录，而不用等函数全部执行完，再返回到calling context。这个好处是减少了大量的PGA使用，从而节省了内存的分配和使用。

在传输大数据量的时候，对于性能的提升特别有效。pipelined表函数还可以使用parallel处理。

要注意的是 pipelined table funcation返回target table不能是delete，insert 或者update语句。

二. 测试

这里我们运行下文档里的几个示例。

2.1 Test 1

SYS@anqing2(rac2)> CREATE TABLE gradereport(

2 student VARCHAR2(30),

3 subject VARCHAR2(30),

4 weight NUMBER,

5 grade NUMBER

6 );

Table created.

SYS@anqing2(rac2)> INSERT INTOgradereport (student, subject, weight, grade) VALUES ('Mark', 'Physics', 4, 4);

1 row created.

SYS@anqing2(rac2)> INSERT INTO gradereport (student, subject,weight, grade) VALUES ('Mark','Chemistry', 4, 3);

1 row created.

SYS@anqing2(rac2)> INSERT INTO gradereport (student, subject,weight, grade) VALUES ('Mark','Maths', 3, 3);

1 row created.

SYS@anqing2(rac2)> INSERT INTOgradereport (student, subject, weight, grade) VALUES ('Mark','Economics', 3,4);

1 row created.

SYS@anqing2(rac2)> commit;

Commit complete.

SYS@anqing2(rac2)> CREATE PACKAGEpkg_gpa IS

2 TYPE gpa IS TABLE OF NUMBER;

3 FUNCTIONweighted_average(input_values SYS_REFCURSOR)

4 RETURN gpa PIPELINED;

5 END pkg_gpa;

6 /

Package created.

CREATE PACKAGE BODY pkg_gpa IS

FUNCTIONweighted_average (input_values SYS_REFCURSOR)

RETURN gpa PIPELINED

IS

grade NUMBER;

total NUMBER := 0;

total_weight NUMBER := 0;

weight NUMBER := 0;

BEGIN

LOOP

FETCH input_values INTO weight, grade;

EXIT WHEN input_values%NOTFOUND;

total_weight := total_weight + weight;-- Accumulate weighted average

total := total + grade*weight;

END LOOP;

PIPEROW (total / total_weight);

RETURN; -- returns single result

ENDweighted_average;

END pkg_gpa;

/

调用pipelined table functions：

SYS@anqing2(rac2)> SELECT w.column_value"weighted result" FROM TABLE (pkg_gpa.weighted_average (CURSOR(SELECT weight, grade FROM gradereport))) w;

weighted result

---------------

3.5

2.2 Test 2

SYS@anqing2(rac2)> CREATE OR REPLACEPACKAGE pkg1 AS

2 TYPE numset_t IS TABLE OFNUMBER;

3 FUNCTION f1(x NUMBER) RETURNnumset_t PIPELINED;

4 END pkg1;

5 /

Package created.

CREATE PACKAGE BODY pkg1 AS

--FUNCTION f1 returns a collection of elements (1,2,3,... x)

FUNCTIONf1(x NUMBER) RETURN numset_t PIPELINED IS

BEGIN

FOR i IN 1..x LOOP

PIPE ROW(i);

END LOOP;

RETURN;

ENDf1;

END pkg1;

/

SYS@anqing2(rac2)> SELECT * FROMTABLE(pkg1.f1(5));

COLUMN_VALUE

------------

1

2

3

4

5

在如下blog 上也有2个示例：

http://blog.csdn.net/BOBO12082119/article/details/6394458

-------------------------------------------------------------------------------------------------------