Example VTICosting implementation

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 ); }
}