Menu
I am new to the concept of Pipeline Functions. I have some questions regarding
From Database point of view:
Thanks.
975
Pipelining enables a table function to return rows faster and can reduce the memory required to cache a table function's results. A pipelined table function can return the table function's result collection in subsets. The returned collection behaves like a stream that can be fetched from on demand.
The PIPE ROW statement, which can appear only in the body of a pipelined table function, returns a table row (but not control) to the invoker of the function. A pipelined table function is declared with the option "PIPELINED".
What Is a Table Function? A table function is a function that can be invoked inside the FROM clause of a SELECT statement. They return collections (usually nested tables or varrays), which can then be transformed with the TABLE clause into a dataset of rows and columns that can be processed in a SQL statement.
Polymorphic table functions (PTFs) are a subset of table functions where the schema of the returned table is determined dynamically. The returned table schema can depend on the arguments you pass to the function.
To quote fom "Ask Tom Oracle":
pipelined functions are simply "code you can pretend is a database table"
pipelined functions give you the (amazing to me) ability to
select * from PLSQL_FUNCTION;anytime you think you can use it -- to select * from a function, instead of a table, it might be "useful".
As far as advantages: a big advantage of using a Pipeline function is that your function can return rows one-by-one as opposed to building the entire result set in memory as a whole before returning it.
The above gives the obvious optimization - memory savings from something that would otherwise return big result set.
A fairly interesting example of using pipelined functions is here
What seems to be a good use of them is ETL (extract/transform/load) - for example see here
81
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
