Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!

Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this series.

Here's my plan for the series:

1. I will start (in this post) with some very simple examples and exploration of a few use cases.

2. Explore table functions that return more than one value per row (via object types).

3. Take a look at how the PL/SQL Challenge uses table functions to avoid code redundancy and minimize the number of interactive reports needed to provide rankings.

4. Streaming table functions: these kinds of table functions are used typically in data warehousing environments, in which one or more transformations of data are needed all "streamed" within a single SQL statement.

5. Pipelined table functions: how they differ from non-pipelined, what they look like, why you would use them.

6. Optimizing execution of table functions: it helps to provide the cost-based optimizer with some additional information so that it can come up with the best plan.

The Basic Idea

I assume you are familiar with the SQL SELECT statement:

SELECT column_list FROM table_or_view1 [, table_or_view2...]

Several versions back of Oracle Database, Oracle added the table operator, which transforms a collection's contents into a relational dataset that can be consumed by a SELECT. The syntax looks like:

SELECT column_list FROM TABLE (my_collection)

and you can also call a function from within the TABLE operator that returns a collection, as in:

SELECT column_list FROM TABLE (my_function (...))

Which means you can emulate a "parameterized view", since you can pass values to the function's parameter list) based on a TABLE operator.

Now, a "true" parameterized view might look something like this:

CREATE VIEW my_view (deparment_id_in in INTEGER)
AS 
   SELECT column_list FROM TABLE (my_function (department_id_in))

But that is not supported in Oracle Database. Instead, a "parameterized view" is, in effect, the table function itself. Your report (or wherever else you need the "parameterized view") would look more or less like:

SELECT * FROM TABLE (my_function (param1, param2, ...))

This way, the dataset returned by the table function is determined by values passed to the function at run-time, rather than at the time a view built around the table function was created.

In addition, in the FROM clause, you can also "blend" together  table function calls, tables and views, as in:

SELECT column_list 
  FROM TABLE (my_collection) t1,
       employees e,
       TABLE (another_collection) t2

'cause at that point, it (the dataset returned by a table function) is just the same as the dataset from a table or view. You can use ORDER BY, GROUP BY, etc. on the data returned from the function.

Prior to Oracle Database 12c Release 1, only nested tables and varrays could be consumed by the TABLE operator, and their types had to be defined at the schema level (CREATE TYPE) or in a package specification.

In 12.1 and higher, you can also use TABLE with integer-indexed associative arrays (also known as "IBIs" because of their INDEX BY clauses), one of my favorite 12.1 enhancements for PL/SQL.

By the way, I expect it is rather clear by now that you need to know about PL/SQL collections in order to use table functions. You don't need to necessarily know all the details, but here are some links to help you along:

Collections documentation

PL/SQL Channel videos on collections (5+ hours' worth!)

Use Cases

I am hoping that your eyes lit up at even the most basic presentation of this feature above, and your mind is spinning with ideas of how to  use table functions. To help you along, though, I offer the following list, all of which will be explored as we move through this series.

Merge session-specific data with data from tables

You've got data, and lots of it sitting in tables. But in your session (and not in any tables), you have some data - and you need to "merge" these two sources together in an SQL statement. In other words, you need the set-oriented power of SQL to get some answers.

With the TABLE operator, you can accomplish precisely that.

Programmatically construct a dataset to be passed as rows and columns to the host environment

Your webpage needs to display some data in a nice neat report. That data is, however, far from neat. In fact, you need to execute procedural code to construct the dataset. Sure, you could construct the data, insert into a table, and then SELECT from the table.

But with a table function, you can deliver that data immediately to the webpage, without any need for non-query DML.

Create (what is in effect) a parameterized view

One of my favorites, and arises (for me) directly from my work on the PL/SQL Challenge. We have lots of different ranking reports, based on different materialized views (but all very similar in their columns and the way the data is computed). Used to be, we created something like 25 different interactive reports in Application Express.

Then, when facing the need to enhance each and every one of those, we stepped back and looked for ways to avoid this repetitive mess. The answer lay in a table function. Since you call a function in the FROM clause, you can pass parameters to the function and therefore to the query itself. That flexibility made it possible to replace those 25 different reports with just 1 report, built on that "parameterized view."

Improve performance of parallelized queries (pipelined table functions)

Many data warehouse applications rely on Parallel Query to greatly improve performance of massive ETL operations. But if you execute a table function in the FROM clause, that query will serialize (blocked by the call to the function). Unless, unless....you define that function a a pipelined function and enable it for parallel execution.

Reduce consumption of Process Global Area (pipelined table functions)

Collections (which are constructed and returned by "normal" table functions) can consume an  awful lot of PGA (Process Global Area). But if you define that table function as pipelined, PGA consumption becomes a non-issue.

Do you know of others? Tell me in a comment on the post and I will add them to this post and give you credit (of course)!

Simple Examples 

I will close out this initial post of the series with some examples of the simplest sort of table functions: those that return a collection of scalar values - a list of strings, dates, numbers, etc.

Since a table function returns a collection, the type of collection must first be defined. Prior to 12.1, that type must be defined as a schema-level type, such as:

CREATE OR REPLACE TYPE list_of_names_t
   IS TABLE OF VARCHAR2 (100);
/

And that's all I need to have some fun. I will now define a function that returns the collection:

CREATE OR REPLACE FUNCTION my_family
   RETURN list_of_names_t
IS
   happyfamily   list_of_names_t
                    := list_of_names_t ('Veva',
                                        'Chris',
                                        'Lauren',
                                        'Loey',
                                        'Eli',
                                        'Steven');
BEGIN
   RETURN happyfamily;
END;
/

And then....I can SELECT FROM that function!

SELECT COLUMN_VALUE family_member
  FROM TABLE (my_family () )
/

FAMILY_MEMBER
-------------------
Veva
Chris
Lauren
Loey
Eli
Steven

Note that when you return a collection of scalars, Oracle automatically uses "COLUMN_VALUE" for the name of the column. You can change it to whatever you'd like with a column alias.

Starting with 12.1, you can also define a collection type returned by a table function in a package specification, and then reference that collection inside a TABLE operator (note one exception to this rule: use of the collection with pipelined table functions):

CREATE OR REPLACE PACKAGE plch_pkg
IS
   TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
END;
/

CREATE OR REPLACE FUNCTION my_family
   RETURN plch_pkg.list_of_names_t
IS
   /* Only works on 12.1 and higher */
   happyfamily plch_pkg.list_of_names_t
                    := plch_pkg.list_of_names_t ('Veva',
                                        'Chris',
                                        'Lauren',
                                        'Loey',
                                        'Eli',
                                        'Steven');
BEGIN
   RETURN happyfamily;
END;
/

DECLARE
   l   INTEGER;
   t   plch_pkg.list_of_names_t := my_family ();
BEGIN
   SELECT COUNT (*) INTO l FROM TABLE (t);

   DBMS_OUTPUT.put_line (l);
END;
/

Note, however, that as of 12.1, you cannot call the table function directly inside the TABLE operator. You must invoke it in PL/SQL, assign result to a variable, and then reference the variable inside TABLE. [thanks to Iudith Mentzel for reminding me of this point - see Comments]

And if you try this prior to 12.1, you will see this error:

ORA-21700: object does not exist or is marked for delete

So upgrade to Oracle Database 12c, already, willya? :-)

As I'd mentioned earlier, once you've stuck that table function inside TABLE inside FROM, well, it's a query like any other. You can create a view over it, you can join with other tables, use it with set operators, etc.:

CREATE VIEW family_members_v
IS
   SELECT COLUMN_VALUE family_member
     FROM TABLE (my_family () )
/

View created

DECLARE
   happyfamily list_of_names_t := 
      list_of_names_t ('Larry', 'Mark', 'Safra');
BEGIN
   FOR rec IN (  SELECT COLUMN_VALUE a_name
                   FROM TABLE (happyfamily)
                 UNION
                 SELECT last_name FROM employees
                  WHERE)
   LOOP
      DBMS_OUTPUT.put_line (rec.a_name);
   END LOOP;
END;
/

De Haan
King
Kochhar
Larry
Mark
Safra

DECLARE
   happyfamily list_of_names_t := 
      list_of_names_t ('Kingston', 'Bellman');
BEGIN
   FOR rec IN (  SELECT DISTINCT e.last_name
                   FROM TABLE (happyfamily) hf, employees e
                  WHERE hf.COLUMN_VALUE like e.last_name || '%')
   LOOP
      DBMS_OUTPUT.put_line (rec.last_name);
   END LOOP;
END;
/

King
Bell

I am not, by the way, claiming that the last example makes any sense. Just showing that you can do it.

And there you have it: a quick introduction, description of (some) use case, and a set of simple examples.

Next in series: Explore table functions that return more than one value per row (via object types).

Questions? Ask 'em below!
Complaints? State 'em below!
Suggestions for improvement? Let 'em rip!

And if you'd like to dive right in to more interesting examples, download my demo.zip and check out the files starting with "tabfunc".

Links to Table Function Series

Table Functions, Part 1: Introduction and Exploration

Table Functions, Part 2: Returning complex (non-scalar) datasets

Table Functions, Part 3a: table functions as parameterized views in the PL/SQL Challenge website

Table Functions, Part 3b: implementing table functions for PL/SQL Challenge reports

Table Functions, Part 4: Streaming table functions

Table Functions, Part 5a: An introduction to pipelined table functions