Selecting from and updating the same table oracle
Execution of a table function can be parallelized, and returned rows can be streamed directly to the next process without intermediate staging.Rows from a collection returned by a table function can also be —that is, iteratively returned as they are produced instead of in a batch after all processing of the table function's input is completed.The co-routine execution model provides a simpler, native PL/SQL mechanism for implementing pipelined table functions, but this model cannot be used for table functions written in C or Java. The interface approach requires the producer to save the current state information in a "context" object before returning so that this state can be restored on the next invocation.In the rest of this chapter, the term to fetch the input rows, perform some transformation on them, and then pipeline the results out (using either the interface approach or the native PL/SQL approach).
During query execution, the method is invoked repeatedly to iteratively retrieve the results.
Streaming, pipelining, and parallel execution of table functions can improve performance: Figure 13-1 shows a typical data-processing scenario in which data goes through several (in this case, three) transformations, implemented by table functions, before finally being loaded into a database.
In this scenario, the table functions are not parallelized, and the entire result collection must be staged after each transformation.
The collection type if it is consumed by a consumer (transformation) as soon as the producer (transformation) produces it, without being staged in tables or a cache before being input to the next transformation.
Pipelining enables a table function to return rows faster and can reduce the memory required to cache a table function's results.