Building a transformative function

A transformative function is a pipelined function that accepts as a parameter a result set (via a CURSOR expression) and returns a result set. This functionality is also new to Oracle9i and can have a very positive effect on application performance.

You can define a table function with an IN argument of type REF CURSOR and call it with a CURSOR expression as the actual parameter—all inside a SQL statement. This technique allows you to "pipe" data from one function to the next, or from one SQL operation to the next, without needing to rely on any intermediate storage of data. Here is an example of how you can use this functionality:

All the code shown here may be found in the tabfunc.sql script on the O'Reilly site.

Consider the following scenario. I have a table of stock ticker information that contains a single row for the openand close prices of stock:

I need to transform (or pivot) that information into another table:

In other words, a single row in StockTable becomes two rows in TickerTable.

There are many ways to achieve this goal. For example, when using traditional methods in pre-Oracle9i versions of the database, I could write code like this:

It works, but for very large volumes of data, perhaps it is not as efficient as it could be. Let's see if I can use a transformative function to do the job more quickly.

I create a collection type to use in my function:

I then create a package defining a REF CURSOR type based on this collection type. I do this in a package specification so that it can be referenced by my function:

And here is my stock pivot function:

And here is that same function defined in a nonpipelined way:

So many different approaches to solving the same problem! How does one choose among them? Well, if you have lots of data to process, you will certainly want to choose the most efficient implementation. You will find in the tabfunc.sql file^[1] an anonymous block that compares the performance of the following four approaches:

^[1] The tabfunc.sql file relies on the PL/Vision timer mechanism, PLVtmr, to calculate elapsed time. You can install this package by running the :plvtmr.pkg script.

· A direct insert from SELECT using the pipelined function:

· A direct insert from SELECT using the nonpipelined function:

· A deposit of pivoted data into a local collection. You can then use a simple loop to transfer the collection's contents to the table:

· The "old-fashioned" method. Use a cursor FOR loop to expand each single row from stocktable into the two rows of the tickertable:

When I execute the block comparing these four aproaches in my SQL*Plus session, I see these results:

I draw two conclusions from this output:

· The ability of the table function (whether pipelined or regular) to transform data "in-line" (i.e., within a single SQL statement) noticeably improves performance.

· Pipelining doesn't help us in this scenario; it actually seems to slow things down a bit. In fact, I am not really taking advantage of pipelining in this code. In all cases, I am waiting until the logic has executed to completion before I do anything with my data (or compute elapsed time).

I would expect (or hope, at least) to see some improvement in elapsed time when executing this logic in parallel or, more generally, when we get the first N number of rows and start processing them before all of the data has been retrieved. The file tabfunc.sql offers a simulation of such a scenario.

I compare the time it takes to execute each of these statements:

And the contrasting timings are very interesting:

Clearly, piping rows back does work and does make a difference!

• ⇐ Назад
178
179
180
181
182
183
184185186
187
188
189
190
191
192
193
Далее ⇒