Database: Pipelined Functions

Question

I am new to the concept of Pipeline Functions. I have some questions regarding

From Database point of view:

• What actually is Pipeline function ?
• What is the advantage of using Pipeline Function ?
• What challenges are solved using Pipeline Function ?
• Are the any optimization advantages of using Pipeline Function ?

Thanks.

Answer 1

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