In January 2009, I wrote a section on performance tuning with pipelined functions for Steven Feuerstein's Oracle PL/SQL Programming, Fifth Edition.My contribution appears in Chapter 21 of the book, but I've re-printed it here for readers, courtesy of O'Reilly Media, Inc.Note that the following article contains references to other chapters in the book as well as the source code that I wrote to accompany the topic.Pipelined functions are where the elegance and simplicity of PL/SQL converge with the performance of SQL.How do i know which column and what is the function used, from the database. How would the index help me in this case since I have to read every single stock item (is it because the index has pre-calculated the value) , run the fucntion and return the result.The report is taking 25 seconds for the web page but I may get it faster with this.
So Oracle must "switch" between them; these are called context switches and Oracle has paid lots of attention over the years to reduce the cost of a single context switch.
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.
Companies like Oracle continue to invest in their products, and features that were once considered slower but more convenient are often improved upon to make them at least as fast as the traditional approaches.
We pause to remark that the loop, which — as we have seen just now — can be based on either an explicit or implicit cursor, that iterates over a large data set can quickly become a huge performance problem.