Oracle PL/SQL Programming, 5th Edition

Improving Performance With Pipelined Table Functions

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