In January 2009, Adrian Billington wrote a section on performance tuning with pipelined functions for Steven Feuerstein’s Oracle PL/SQL Programming, Fifth Edition (you can download the eBook version for free for a limited period from Red Gate Software). Adrian’s contribution appears in Chapter 21 of the book, but he’s re-printed it on, courtesy of O’Reilly Media, Inc. Note that the article contains references to other chapters in the book as well as the source code that Adrian wrote to accompany the topic.

Pipelined functions are where the elegance and simplicity of PL/SQL converge with the performance of SQL. Complex data transformations are effortless to develop and support with PL/SQL, yet to achieve high-performance data processing, we often resort to set-based SQL solutions. Pipelined functions bridge the gap between the two methods effortlessly, but they also have some unique performance features of their own, making them a superb performance optimization tool.

In the following pages, I’ll show some examples of typical data-processing requirements and how you might tune them with pipelined functions. I’ll cover the following topics:

  • How to tune typical data-loading requirements with pipelined functions. In each case, I‘ll convert legacy row-based solutions to set-based solutions that include parallel pipelined functions.
  • How to exploit the parallel context of pipelined functions to improve the performance of data unloads.
  • The relative performance of the partitioning and streaming options for parallel pipelined functions.
  • How the cost-based optimizer (CBO) deals with both pipelined and standard table functions.
  • How complex multitable loading requirements can be solved with multitype pipelined functions.

The basic syntax for pipelined table functions is covered in Chapter 17. To recap, a pipelined function is called in the FROM clause of a SQL statement and is queried as if it were a relational table or other rowsource. Unlike standard table functions (that have to complete all of their processing before passing a potentially large collection of data back to the calling context), pipelined table functions stream their results to the client almost as soon as they are prepared. In other words, pipelined functions do not materialize their entire result set, and this optimization feature dramatically reduces their PGA memory footprint. Another unique performance feature of pipelined functions is the ability to call them in the context of a parallel query. I have taken advantage of these unique performance features many times, and in the next few pages I will show you how and when to use pipelined functions to improve the performance of some of your own programs.

You can read the full article and many more Oracle articles on