Once you have measured your table function's cost, you can write the VTICosting methods.

Optimizer fine-tuning can be added to the EmployeeTable table function shown before in Example Derby-style table function:

package com.acme.hrSchema;

import java.io.Serializable;
import java.sql.*;

import org.apache.derby.vti.VTICosting;
import org.apache.derby.vti.VTIEnvironment;

/**
 * Tuned table function.
 */
public class TunedEmployeeTable extends EmployeeTable 
    implements VTICosting
{
    public TunedEmployeeTable() {}

    public double getEstimatedRowCount( VTIEnvironment optimizerState ) 
        throws SQLException
    {
        return getRowCount( optimizerState );
    }

    public double getEstimatedCostPerInstantiation( 
        VTIEnvironment optimizerState ) throws SQLException
    {
        double I = 100.0;  // optimizer imprecision
        double P = 10.0;   // cost per row in milliseconds
        double E = 0.0;    // cost of instantiating the external 
                            //   ResultSet
        double N = getRowCount( optimizerState );

        return I * ( ( P * N ) + E );
    }
    
    public boolean supportsMultipleInstantiations( 
        VTIEnvironment optimizerState ) throws SQLException
    {
        return true;
    }

    //////////////////////////////////////////////////////////////////

    private double  getRowCount( VTIEnvironment optimizerState )
        throws SQLException
    {
        String ROW_COUNT_KEY = "rowCountKey";
        Double estimatedRowCount = (Double) getSharedState( 
            optimizerState, ROW_COUNT_KEY );
        
        if ( estimatedRowCount == null )
        {
            Connection        conn = getConnection();
            PreparedStatement ps = conn.prepareStatement( 
                "select count(*) from hrSchema.EmployeeTable" );
            ResultSet         rs = ps.executeQuery();

            rs.next();
            estimatedRowCount = new Double( rs.getDouble( 1 ) );
            
            setSharedState( optimizerState, ROW_COUNT_KEY, 
                estimatedRowCount );

            rs.close();
            ps.close();
            conn.close();
        }

        return estimatedRowCount.doubleValue();
    }

    private Serializable getSharedState( 
        VTIEnvironment optimizerState, String key )
    { 
        return (Serializable) optimizerState.getSharedState( key ); 
    }

    private void setSharedState( VTIEnvironment optimizerState, 
        String key, Serializable value )
    { 
        optimizerState.setSharedState( key, value ); 
    }
}