Your source cursor must passed ref cursor parameter

Enabling parallel pipelined function execution

Aside from the parallel-enabling syntax in the specification and body, the function im‐plementation is the same as the array-fetch example (see the stockpivot_setup.sql file on the website for the full package). However, I need to ensure that my tickertable load is executed in parallel. First, I must enable parallel DML at the session level. Once this is done, I invoke parallel query in one of the following ways:

In my load, I have enabled parallel DML at the session level and used hints to specify a degree of parallelism (DOP) of 4:

INSERT /*+ PARALLEL(t, 4) */ INTO tickertable t

(ticker, price_type, price, price_date)

END load_stocks_parallel;

This reduces the load time to just over 3 seconds, a significant improvement on my original legacy code and all other versions of my pipelined function load. Of course, when we’re dealing in small units of time such as this, the startup costs of parallel pro‐cesses will impact the overall runtime, but I have still managed almost a 50% improve‐ment on my array version. The fact that parallel inserts use direct path loads rather than conventional path loads also means that the redo generation dropped further still, to just 25 KB!

Tuning Merge Operations with Pipelined Functions

You might now be considering serial or parallel pipelined functions as a tuning mech‐anism for your own high-volume data loads. Yet not all loads involve inserts like the stockpivot example. Many data loads are incremental and require periodic merges of new and modified data. The good news is that the same principle of combining PL/SQL transformations with set-based SQL applies to merges (and updates) as well.

I’ve removed some of the code for brevity, but you can clearly see the “upsert” technique in action. Note that I’ve used an implicit cursor FOR loop that will benefit from the array-fetch optimization introduced to PL/SQL in Oracle Database 10g.