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
Measuring the cost of Derby-style table functions
and
Example VTICosting implementation:
  
  - getEstimatedCostPerInstantiation() - This method estimates the
            cost of invoking the table function and looping
            through its rows. The returned value adds together
            two estimates:
    
  
- getEstimatedRowCount() - This guesses the number of rows
            returned by invoking the table function.
- supportsMultipleInstantiations() - This returns false if
            the table function
returns different results when invoked more than once.