Home » RDBMS Server » Performance Tuning » Pipelining Table Functions
Pipelining Table Functions [message #242725] Mon, 04 June 2007 21:07 Go to next message
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 Go to previous messageGo to next message
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
Re: Pipelining Table Functions [message #243582 is a reply to message #242875] Thu, 07 June 2007 16:03 Go to previous message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
Thank you Ross.
Previous Topic: Normal Forms
Next Topic: 2 tables same rowcount different size
Goto Forum:
  


Current Time: Thu Dec 05 19:14:11 CST 2024