Optimizer fine-tuning can be added to the EmployeeTable
table function shown before in
:
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 ); }
}