Writing restricted table functions Restricted table functions are -style table functions which perform more efficiently because they can be told in advance which columns they will be asked to fetch along with simple limits on those columns. This feature exploits the expressiveness of the Java programming language and does not require any extensions to SQL. Functionsprogramming table functions

A table function returns a rectangular chunk of data. If you use a restricted table function, can tell the table function to return a shorter and narrower rectangle.

Consider the following scan of a table in a foreign database:

select id, firstName, lastName from table( foreignDatabaseEmployeeTable() ) s where lastName = 'Stone'

If foreignDatabaseEmployeeTable is a restricted table function, can tell the table function to fetch only the id, firstName, and lastName columns. In addition, can tell the table function that it does not need to scan the entire foreign table; instead, the table function only needs to retrieve information for employees whose last name is "Stone".

Depending on the table function and query, this feature can support 1000X, 1000000X, or even greater performance improvements.

How to use restricted table functions

Creating and using a restricted table function involves the following steps:

  1. Implement - You must write a class which implements both java.sql.ResultSet and the -specific interface org.apache.derby.vti.RestrictedVTI. This interface defines an initScan() method. When executing a query, uses that method to tell the table function what columns it will have to fetch and what bounds should be applied to those columns in order to reduce the number of rows returned. For the rest of this discussion, this user-written class will be referred to as MyVTIClass.
  2. Publish - You must publish the table function by creating a public static method which returns a MyVTIClass. This is important. The compiler must be able to see that the table function returns an object which implements both java.sql.ResultSet and org.apache.derby.vti.RestrictedVTI.
  3. Declare - You declare the table function to using the same CREATE FUNCTION syntax you are already familiar with. This syntax does not change.
  4. Invoke - You then use the table function in a query. When compiles the query, it sees that the return type of the table function implements org.apache.derby.vti.RestrictedVTI. Armed with this information, at runtime calls the initScan() method once before calling any of the ResultSet methods.

For example, you would declare the function as follows:

public class MyVTIClass implements ResultSet, RestrictedVTI { ... public void initScan(java.lang.String[] columnNames, org.apache.derby.vti.Restriction restriction ) throws SQLException { ... } }

Then you publish the table function method:

public static MyVTIClass foreignDatabaseEmployeeTable() throws SQLException { ... }

Then you declare the table function to :

create function foreignDatabaseEmployeeTable() returns table ( id int, birthday date, taxPayerID varchar( 50 ), firstName varchar( 50 ), lastName varchar( 50 ) ) language java parameter style DERBY_JDBC_RESULT_SET no sql external name 'com.example.portal.ForeignQueries.foreignDatabaseEmployeeTable'

Finally, you invoke the table function in a query:

select id, firstName, lastName from table( foreignDatabaseEmployeeTable() ) s where lastName = 'Stone'

When you invoke this query, does the following:

  • Prepare - When prepares the query, sees that the foreignDatabaseEmployeeTable() method returns an object which implements org.apache.derby.vti.RestrictedVTI. This is all that needs to know in order to compile a plan which takes advantage of this feature.
  • Execute - When executes the query, calls initScan(). In this example, calls initScan() with the following arguments: initScan( new String[] { "ID", null, null, "FIRSTNAME", "LASTNAME" }, new Restriction.ColumnQualifier( "LASTNAME", ORDER_OP_EQUALS, "Stone" ) )

    This, in turn, causes the following to happen:

    • Width - The call to initScan() told the table function what columns should be fetched.
    • Length - The call to initScan() told the table function how to filter the rows it returns.
    • Loop - then calls MyVTIClass.next() and retrieves rows until MyVTIClass.next() returns false. For each row, calls:
      • MyVTIClass.getInt( 1 ) to get the id column.
      • MyVTIClass.getString( 4 ) to get the firstName column.
      • MyVTIClass.getString( 5 ) to get the lastName column.
Contract

calls initScan() before calling any other method on the ResultSet. The call to initScan() merely passes hints, which the restricted table function can exploit in order to perform better. enforces the restriction outside the table function. Therefore, a restricted table function can still fetch extra columns and can ignore part or all of the restriction set by the call to initScan().

Affected Operations

Compared to ordinary table functions, a restricted table function can perform better in queries involving the following comparisons of its columns to constants:

< <= = != <> > >= IS NULL IS NOT NULL

In addition, performance gains can be realized for queries involving the following operators on the columns of the restricted table function:

LIKE BETWEEN

However, this feature does not boost performance either for the IN operator, or in situations where transforms OR lists into IN lists. See "Or transformations" in for more information.