Oracle Pipelined Table functions
|
Do you want to know/learn?
ü What are Oracle Table functions?
ü How could these tables be pipelined, parallelized and streamed?
ü What are the practical benefits of using these functions?
This article intends to provide an overview of the Pipelined Table functions introduced in Oracle 9i.
Table functions in Oracle:
ü Table functions are functions that take a collection of rows as input and produce a collection of rows (either a nested table or a varray) as the output.
ü An input collection parameter can be either a collection type (such as a VARRAY or a PL/SQL table) or a REF CURSOR.
ü They can be queried like a table by calling the function in the FROM clause of a query.
ü They can be assigned to a PL/SQL collection variable by calling the function in the SELECT list of a query.
Pipelined Table functions in Oracle:
ü A pipelined table function can return the table function's result collection in subsets. The returned collection behaves like a stream that can be fetched on demand.
This makes it possible to use a table function like a virtual table.
ü ‘PIPELINED’ keyword indicates that the function will return rows iteratively.
ü ‘PIPE ROW’ statement causes a table function to pipe a row and continue processing.
ü The statement enables a PL/SQL table function to return rows as soon as they are produced.
ü A pipelined table function must have a blank RETURN statement that does not return a value. The RETURN statement transfers the control back to the consumer and ensures that the next fetch gets a NO_DATA_FOUND exception.
Let us try to understand the above keywords and the pipelined function better with the help of a simple illustration.
Illustration:
Requirement:
Write a function to convert the input of the following type (Ticker, Open Price, Close Price) to a two-step output of the following types(Ticker, PriceType, Price)
i.e, Conversion must be from (“ORCL”, 41, 42) to (“ORCL”, “O”, 41) and (“ORCL”, “C”,42)
Please refer to the attached ‘Pipelined_Functions.sql’, for understanding the table structure, input and output types we will be using in the following example.
Solution through Pipelined functions:
CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet
PIPELINED IS -- indicates that the function will return rows iteratively
out_rec TickerType := TickerType(NULL,NULL,NULL);
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;
-- first row
out_rec.ticker := in_rec.Ticker;
out_rec.PriceType := 'O';
out_rec.price := in_rec.OpenPrice;
PIPE ROW(out_rec); -- pipes the first row of the two-step output
-- second row
out_rec.PriceType := 'C';
out_rec.Price := in_rec.ClosePrice; -- pipes the second row of the two-step output
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN; -- Blank return statement to indicate the transfer of control back to the caller
END;
How does this Pipelining differ from our conventional approach:
Consider the table to have 2 rows, so we are now expecting 4 rows from this function. In both the methods, let us compare the time when these rows are returned. Source.
Advantages & Other features:
ü Improved query response time: Normal functions wait for the entire result set to be constructed and then return to the server, the entire result at once. Whereas, here the rows are returned as and when processed, thereby improving the query response time.
ü Saves memory: Consider for Eg., we have one lakh rows as input to an non-pipelined function, we need to store the two lakh output rows in our PGA memory before it is returned as a single result set. Here, since we are iteratively sending the output rows in subsets, we save a huge amount of memory.
ü Eliminates intermediate staging: Pipelined table functions takes bulk data set as input and makes all the necessary transformation within the function and has the ability to stream the output result set to the next process without intermediate staging. This process is referred to as Streaming. More on streaming.
ü Improved performance by parallelization: pipelined functions can be parallelized by Oracle which means that PL/SQL can be executed by multiple slaves in parallel, thereby reducing the processing time drastically. More on parallelization.
As a gist, Oracle Table functions can be considered to bring in significant performance improvements by using the concepts of Pipelining, Parallelization and Streaming!
|
