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