I am trying to get a handle on understanding and using pipelining with table functions. We have very high data flow into the tables and need to improve processing speed. I've read the documentation but it is not really making sense to me. Will pipelining return the entire row of a table queried, or just a collection objects and values?
Answer:
James, pipelined functions can be very handy for improving the performance of queries that need to run in parallel, but I am not sure how useful they are in speeding up the performance of data loads into tables.
Here is the skinny on pipelined functions:
• Prior to Oracle9i Release 2 and support for pipelined functions (example below), whenever you called a PL/SQL function inside a query, the execution of that query would serialize. In other words, only one instance of that query could run on one processor. Therefore if you were utilizing Parallel Query, which many Oracle customers do when working with data warehouses, parallelization would be disabled for that query. Now, you can define a pipelined function so that it, too, executes in parallel, thereby potentially speeding up the execution of your query that contains the function.
• Pipelined functions must take as their incoming parameter a cursor result set, in the form or a REF CURSOR. They must return a nested table of object types. Each row in a nested table could conform to a row in a relational table, but it doesn't have to; the whole idea here is that you can now apply arbitrarily complex PL/SQL functions to result sets.
This question intersects nicely with the answer to the "Performance Makeover" puzzle. In that solution, I talk about transformative table functions and offer the pipelined stockpivot function as another way to speed up the pivot operation.
You know you've got a pipelined function when the header of the function includes the PIPELINED clause and the function uses PIPE ROW to "pipe" data out from the function, asynchronous to termination of the function.
No comments:
Post a Comment