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 ...