Section 3.1. Why Table Functions?
3.1. Why Table Functions?Let's start with a simple example of what table functions look like and what they can do for you. 3.1.1. A Simple ExampleEarlier, I mentioned the idea of querying a table function with a SELECT statement. Here is an example. SELECT * FROM TABLE(company_balance_sheet); At first glance, this might look like just another query, but note that company_balance_sheet is a function. Let's suppose here that it is a function that can peruse millions of lengthy accounting records from potential acquisitions to see how they would affect a parent company's bottom line. The large amount of data and stringent accounting rules may seem best suited to a program all their own, but what if the results must be available via a simple query from a web page? Table functions to the rescue... Here is an example of a table function embedded in PL/SQL. Notice that here it is used just like any other cursor. However, this function might be scanning mountains of detailed sales transactions to summarize them in real time by region to enable managers to make decisions toward make-or-break sales quotas. DECLARE CURSOR curs_get_western_sales IS SELECT * FROM TABLE(total_sales_by_region) WHERE region = 'Western'; v_western_sales NUMBER; BEGIN OPEN curs_get_western_sales; FETCH curs_western_sales INTO v_western_sales; CLOSE curs_get_western_sales; END; This example shows how a table function can accept parameters that may be used to control business processing. In the next example, the function examines reams of intricate test samples looking for anomalies before presenting results that must be accurate for real-time grant requests aimed at funding important cancer research: SELECT * FROM TABLE(cancer_research_results( sdate => SYSDATE, edate => SYSDATE + 1 ); On a much less grand scale (but just as important to your own business), you might use a table function in a query that needs to apply complex logic to find all unscheduled work orders within three seconds so the calling application won't time out. Or you might use a table function to prevent a simple .NET query screenone that needs to display all available backup equipmentfrom being seen as unreliable because it takes too long to work through a formula to find replacement parts for a broken water main. In the world of data transformation processing, table functions allow results from nested transformations to be sent downstream for further work, one by one. There are many situations in which a long transformation is essentially waiting on itself, because the full result set from an initial transformation must be assembled before further transformations can even starteven though they could logically begin as soon as the first record is available. You can use table functions in a mode known as pipelining to handle this situation, and you can use parallelized table functions to throw even more power at a transformation. You're still not sure that table functions make the DBA's life easier? Well, consider the fact that table functions allow you to embed actual business logic into a query to ensure that it does only what it absolutely has to. For example, instead of worrying about rollback segment space for frequent long-running queries, you could cache the results in memory, knowing that the underlying data set changes only hourly. You can also implement performance features like bulk fetching and use of associative arrays (formerly known as index-by tables) within table functions to reduce overall stress on a database. We'll look at these and other examples in this chapter. First, we'll cover some basics. 3.1.2. Calling a Table FunctionMost DBAs know that Oracle allows functions to be called in queries like the following: SELECT SYSDATE FROM DUAL; Oracle permits this type of query because the structure of its return set is defined: it will return a single column of type DATE in a single record. Most DBAs also know that for any object (e.g., a table, a view, or another type of object) to be part of a SELECT statement, it must have a defined result set structure. Otherwise, the database will have no idea in what format the results will be returned. But you may be wondering how functions that have historically returned single scalar values can possibly return a multi-column, multi-record result set like this: SQL> 3.1.3. Defining the Result Set StructurePL/SQL is pretty smart about resolving the result set of a table or a view. The language makes it easy for us not to worry about datatypes by substituting %TYPE and %ROWTYPE variables as follows. DECLARE v_order_row orders%ROWTYPE; BEGIN SELECT order_id, region_id INTO v_order_row FROM orders; END; However, PL/SQL will be at a loss to decipher the structure returned by a table function because it has no basis from which to work. You must provide that basis explicitly via Oracle objects and collections. This is illustrated in this example of declaring an object and then a collection of that object. CREATE TYPE rowset_o AS OBJECT ( col1 NUMBER, col2 VARCHAR2(30)); / CREATE TYPE rowset_t AS TABLE OF rowset_o; / The "multi-recordness" of the collection is what allows it to be used as the result set of a function. CREATE OR REPLACE FUNCTION simple RETURN rowset_t AS v_rowset rowset_t := rowset_t( ); BEGIN v_rowset.EXTEND(3); v_rowset(1) := rowset_o(1,'Value 1'); v_rowset(2) := rowset_o(2,'Value 2'); v_rowset(3) := rowset_o(3,'Value 3'); RETURN(v_rowset); END; All the function does is assemble three objects into a collection and return them. Now the function can be called from a SELECT statement using the TABLE keyword to tell Oracle to treat the returned collection as if it were a set of records. SQL> SELECT * 2 FROM TABLE(simple); COL1 COL2 ---------- ---------------- 1 Value 1 2 Value 2 3 Value 3 3 rows selected. The full power of Oracle SQL can be applied to the result set returned by a PL/SQL table function, just as if a table or a view had been queried. SQL> SELECT * 2 FROM TABLE(simple) 3 WHERE col1 = 2; COL1 COL2 ---------- -------------- 2 Value 2 SQL> SELECT col2 2 FROM TABLE(SIMPLE) 3 GROUP BY col2; COL2 ------------------------- Value 1 Value 2 Value 3 A table function can perform any work that could be performed in a standard function, including queries and conditional logic. The topic of table functions is a very large one, so, in this chapter, I won't be able to explore in detail every possible application of table functions. All of the applications I've mentioned, however, share a common requirement: they take advantage of the temporary and fast storage of data (in a collection). For example, consider the cancer research application we mentioned earlier. Each research experiment record needs to be evaluated for completeness before being considered for inclusion in the final result set, and every individual experiment has to be evaluated for completeness before any subsequent processing can occur. If 1,000 experiments have to be looked at, and each takes 2 seconds to evaluate for completeness, it will be more than half an hour (2,000 seconds) before any upstream processing can occur. This application would be far more efficient if each experiment could be passed upstream for subsequent processing as soon as it passed evaluation. Table functions make this possible, as I describe in the next section. |
