Optimizer support for Derby-style table functions
Derby Developer's Guide
56
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 Derby transforms OR lists into IN lists. See "Or transformations" in
Tuning Derby for more information.
Optimizer support for Derby-style table functions
This topic explains how to fine-tune the Derby optimizer's decision about where to place a
table function in the join order.
By default, the Derby optimizer makes the following assumptions about a table function:
· Expensive - It is expensive to create and loop through the rows of the table
function. This makes it likely that the optimizer will place the table function in an
outer slot of the join order so that it will not be looped through often.
· Repeatable - The table function can be instantiated multiple times with the same
results. This is probably true for most table functions. However, some table
functions may open read-once streams. If the optimizer knows that a table function
is repeatable, then the optimizer can place the table function in an inner slot where
the function can be invoked multiple times. If a table function is not repeatable, then
the optimizer must either place it in the outermost slot or invoke the function once
and store its contents in a temporary table.
The user can override this optimizer behavior by giving the optimizer more information.
Here's how to do this:
· No-arg constructor - The table function's class must have a public constructor
whose signature has no arguments.
· VTICosting - The class must also implement org.apache.derby.vti.VTICosting. This
involves implementing the following methods as described in
· getEstimatedCostPerInstantiation() - This method estimates the cost of
invoking the table function and looping through its rows. The returned value
adds together two estimates:
· Empty table - This is the cost of invoking the table function, even if it
contains 0 rows. See the description of variable E in
.
