| Pipelining Table Functions [message #242725] |
Mon, 04 June 2007 21:07  |
artmt
Messages: 32
Registered: October 2006
Location: Boston
|
Member |
|
|
Suppose I make it a habit to code all my table functions with the PIPELINED clause. Some of them may not be “pipelineable” (one reason could be the SORT operations involved).
Is there any down side to coding such functions as PIPELINED?
I am guessing there must be some disadvantage. Otherwise, pipelining would be the default behavior.
|
|
|
|
| Re: Pipelining Table Functions [message #242875 is a reply to message #242725] |
Tue, 05 June 2007 06:43  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Pipelining returns data via the PIPE ROW statement, whereas non-pipelined table functions return once at the end via the RETURN statment. Given this, I don't see how pipelining could be intelligently introduced as the default behaviour.
Pipelining can still confer an advantage if you sort with an ORDER BY because it does not need to store the entire result (again) in an array before it returns the data - this would save memory for a big table.
It would not confer an advantage to pipeline if you had to load the results into a nested table anyway so that you could perform some PL/SQL processing on all of the rows collectively before returning a single row. In this case you've already chewed up the memory, why cycle through the nested table again just to PIPE ROW every row?
If you have no reason to load the results into a nested table for PL/SQL processing, then I don't think you will be troubled by creating all table functions as pipelined.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
