org.apache.derby.iapi.sql.compile
Interface Optimizer

All Known Implementing Classes:
OptimizerImpl

public interface Optimizer

Optimizer provides services for optimizing a query. RESOLVE: o Need to figure out what to do about subqueries, figuring out their attachment points and how to communicate them back to the caller.


Field Summary
static int ADDING_UNORDERED_OPTIMIZABLE
           
static int CALLING_NEXT_ACCESS_PATH
           
static int CALLING_ON_JOIN_NODE
           
static int CHANGING_ACCESS_PATH_FOR_TABLE
           
static int CHEAPEST_PLAN_SO_FAR
           
static int COMPLETE_JOIN_ORDER
           
static int COMPOSITE_SEL_FROM_STATS
           
static int CONSIDERING_CONGLOMERATE
           
static int CONSIDERING_JOIN_ORDER
           
static int CONSIDERING_JOIN_STRATEGY
           
static int COST_INCLUDING_COMPOSITE_SEL_FROM_STATS
           
static int COST_INCLUDING_EXTRA_1ST_COL_SELECTIVITY
           
static int COST_INCLUDING_EXTRA_NONQUALIFIER_SELECTIVITY
           
static int COST_INCLUDING_EXTRA_QUALIFIER_SELECTIVITY
           
static int COST_INCLUDING_EXTRA_START_STOP
           
static int COST_INCLUDING_STATS_FOR_INDEX
           
static int COST_OF_CHEAPEST_PLAN_SO_FAR
           
static int COST_OF_CONGLOMERATE_SCAN1
           
static int COST_OF_CONGLOMERATE_SCAN2
           
static int COST_OF_CONGLOMERATE_SCAN3
           
static int COST_OF_CONGLOMERATE_SCAN4
           
static int COST_OF_CONGLOMERATE_SCAN5
           
static int COST_OF_CONGLOMERATE_SCAN6
           
static int COST_OF_CONGLOMERATE_SCAN7
           
static int COST_OF_N_SCANS
           
static int COST_OF_NONCOVERING_INDEX
           
static int COST_OF_SORTING
           
static int CURRENT_PLAN_IS_SA_PLAN
           
static int ESTIMATING_COST_OF_CONGLOMERATE
           
static int HJ_HASH_KEY_COLUMNS
           
static int HJ_SKIP_NO_JOIN_COLUMNS
           
static int HJ_SKIP_NOT_MATERIALIZABLE
           
static int ILLEGAL_USER_JOIN_ORDER
           
static java.lang.String JOIN_ORDER_OPTIMIZATION
          Property name for controlling whether to do join order optimization.
static int LOOKING_FOR_SPECIFIED_INDEX
           
static int MATCH_SINGLE_ROW_COST
           
static java.lang.String MAX_MEMORY_PER_TABLE
          Property name for controlling the maximum size of memory (in KB) the optimizer can use for each table.
static int MODIFYING_ACCESS_PATHS
           
static java.lang.String MODULE
          Module name for the monitor's module locating system.
static int NO_BEST_PLAN
           
static int NO_MORE_CONGLOMERATES
           
static int NO_TABLES
           
static java.lang.String NO_TIMEOUT
          Property name for controlling whether the optimizer ever times out while optimizing a query and goes with the best plan so far.
static int NON_COVERING_INDEX_COST
           
static int NORMAL_PLAN
          Indicates a "normal" plan that is not optimized to do sort avoidance
static int PLAN_TYPE
           
static int REMEMBERING_BEST_ACCESS_PATH
           
static int REMEMBERING_BEST_ACCESS_PATH_SUBSTRING
           
static int REMEMBERING_BEST_JOIN_ORDER
           
static int REMEMBERING_BEST_SORT_AVOIDANCE_ACCESS_PATH_SUBSTRING
           
static int REMEMBERING_BEST_UNKNOWN_ACCESS_PATH_SUBSTRING
           
static int REMEMBERING_JOIN_STRATEGY
           
static int ROW_LOCK_ALL_CONSTANT_START_STOP
           
static int ROW_LOCK_UNDER_THRESHOLD
           
static java.lang.String RULE_BASED_OPTIMIZATION
          Property name for controlling whether to do rule-based optimization, as opposed to cost-based optimization.
static int SCANNING_HEAP_FULL_MATCH_ON_UNIQUE_KEY
           
static int SHORT_CIRCUITING
           
static int SKIPPING_DUE_TO_EXCESS_MEMORY
           
static int SKIPPING_JOIN_ORDER
           
static int SORT_AVOIDANCE_PLAN
          Indicates a sort-avoidance plan
static int SORT_NEEDED_FOR_ORDERING
           
static int STARTED
           
static int TABLE_LOCK_NO_START_STOP
           
static int TABLE_LOCK_OVER_THRESHOLD
           
static int TIME_EXCEEDED
           
static int TOTAL_COST_NON_SA_PLAN
           
static int TOTAL_COST_SA_PLAN
           
static int TOTAL_COST_WITH_SORTING
           
static java.lang.String USE_STATISTICS
          Property name for disabling statistics use for all queries.
static int USER_JOIN_ORDER_OPTIMIZED
           
 
Method Summary
 void considerCost(Optimizable optimizable, OptimizablePredicateList predList, CostEstimate estimatedCost, CostEstimate outerCost)
          Consider the cost of the given optimizable.
 void costOptimizable(Optimizable optimizable, TableDescriptor td, ConglomerateDescriptor cd, OptimizablePredicateList predList, CostEstimate outerCost)
          Cost the current Optimizable with the specified OPL.
 void costPermutation()
          Cost the current permutation.
 DataDictionary getDataDictionary()
          Return the DataDictionary that the Optimizer is using.
 JoinStrategy getJoinStrategy(int whichStrategy)
          Gets a join strategy by number (zero-based).
 JoinStrategy getJoinStrategy(java.lang.String whichStrategy)
          Gets a join strategy by name.
 int getLevel()
          Get the level of this optimizer.
 int getMaxMemoryPerTable()
           
 boolean getNextDecoratedPermutation()
          Iterate through the "decorated permutations", returning false when they are exhausted.
 boolean getNextPermutation()
          Iterate through the permutations, returning false when the permutations are exhausted.
 int getNumberOfJoinStrategies()
          Get the number of join strategies supported by this optimizer.
 CostEstimate getOptimizedCost()
          Get the estimated cost of the optimized query
 void modifyAccessPaths()
          Modify the access path for each Optimizable, as necessary.
 CostEstimate newCostEstimate()
          Get a new CostEstimate object
 void setOuterRows(double outerRowCount)
          Set the estimated number of outer rows - good for optimizing nested optimizables like subqueries and join nodes.
 int tableLockThreshold()
          Get the maximum number of estimated rows touched in a table before we decide to open the table with table locking (as opposed to row locking.
 void trace(int traceFlag, int intParam1, int intParam2, double doubleParam, java.lang.Object objectParam1)
          Optimizer trace.
 double uniqueJoinWithOuterTable(OptimizablePredicateList predList)
          Tells whether any of the tables outer to the current one has a uniqueness condition on the given predicate list, and if so, how many times each unique key can be seen by the current table.
 boolean useStatistics()
          If statistics should be considered by the optimizer while optimizing a query.
 

Field Detail

MODULE

public static final java.lang.String MODULE
Module name for the monitor's module locating system.

See Also:
Constant Field Values

JOIN_ORDER_OPTIMIZATION

public static final java.lang.String JOIN_ORDER_OPTIMIZATION
Property name for controlling whether to do join order optimization.

See Also:
Constant Field Values

RULE_BASED_OPTIMIZATION

public static final java.lang.String RULE_BASED_OPTIMIZATION
Property name for controlling whether to do rule-based optimization, as opposed to cost-based optimization.

See Also:
Constant Field Values

NO_TIMEOUT

public static final java.lang.String NO_TIMEOUT
Property name for controlling whether the optimizer ever times out while optimizing a query and goes with the best plan so far.

See Also:
Constant Field Values

MAX_MEMORY_PER_TABLE

public static final java.lang.String MAX_MEMORY_PER_TABLE
Property name for controlling the maximum size of memory (in KB) the optimizer can use for each table. If an access path takes memory larger than that size for a table, the access path is skipped. Default is 1024 (KB).

See Also:
Constant Field Values

USE_STATISTICS

public static final java.lang.String USE_STATISTICS
Property name for disabling statistics use for all queries.

See Also:
Constant Field Values

NORMAL_PLAN

public static final int NORMAL_PLAN
Indicates a "normal" plan that is not optimized to do sort avoidance

See Also:
Constant Field Values

SORT_AVOIDANCE_PLAN

public static final int SORT_AVOIDANCE_PLAN
Indicates a sort-avoidance plan

See Also:
Constant Field Values

STARTED

public static final int STARTED
See Also:
Constant Field Values

TIME_EXCEEDED

public static final int TIME_EXCEEDED
See Also:
Constant Field Values

NO_TABLES

public static final int NO_TABLES
See Also:
Constant Field Values

COMPLETE_JOIN_ORDER

public static final int COMPLETE_JOIN_ORDER
See Also:
Constant Field Values

COST_OF_SORTING

public static final int COST_OF_SORTING
See Also:
Constant Field Values

NO_BEST_PLAN

public static final int NO_BEST_PLAN
See Also:
Constant Field Values

MODIFYING_ACCESS_PATHS

public static final int MODIFYING_ACCESS_PATHS
See Also:
Constant Field Values

SHORT_CIRCUITING

public static final int SHORT_CIRCUITING
See Also:
Constant Field Values

SKIPPING_JOIN_ORDER

public static final int SKIPPING_JOIN_ORDER
See Also:
Constant Field Values

ILLEGAL_USER_JOIN_ORDER

public static final int ILLEGAL_USER_JOIN_ORDER
See Also:
Constant Field Values

USER_JOIN_ORDER_OPTIMIZED

public static final int USER_JOIN_ORDER_OPTIMIZED
See Also:
Constant Field Values

CONSIDERING_JOIN_ORDER

public static final int CONSIDERING_JOIN_ORDER
See Also:
Constant Field Values

TOTAL_COST_NON_SA_PLAN

public static final int TOTAL_COST_NON_SA_PLAN
See Also:
Constant Field Values

TOTAL_COST_SA_PLAN

public static final int TOTAL_COST_SA_PLAN
See Also:
Constant Field Values

TOTAL_COST_WITH_SORTING

public static final int TOTAL_COST_WITH_SORTING
See Also:
Constant Field Values

CURRENT_PLAN_IS_SA_PLAN

public static final int CURRENT_PLAN_IS_SA_PLAN
See Also:
Constant Field Values

CHEAPEST_PLAN_SO_FAR

public static final int CHEAPEST_PLAN_SO_FAR
See Also:
Constant Field Values

PLAN_TYPE

public static final int PLAN_TYPE
See Also:
Constant Field Values

COST_OF_CHEAPEST_PLAN_SO_FAR

public static final int COST_OF_CHEAPEST_PLAN_SO_FAR
See Also:
Constant Field Values

SORT_NEEDED_FOR_ORDERING

public static final int SORT_NEEDED_FOR_ORDERING
See Also:
Constant Field Values

REMEMBERING_BEST_JOIN_ORDER

public static final int REMEMBERING_BEST_JOIN_ORDER
See Also:
Constant Field Values

SKIPPING_DUE_TO_EXCESS_MEMORY

public static final int SKIPPING_DUE_TO_EXCESS_MEMORY
See Also:
Constant Field Values

COST_OF_N_SCANS

public static final int COST_OF_N_SCANS
See Also:
Constant Field Values

HJ_SKIP_NOT_MATERIALIZABLE

public static final int HJ_SKIP_NOT_MATERIALIZABLE
See Also:
Constant Field Values

HJ_SKIP_NO_JOIN_COLUMNS

public static final int HJ_SKIP_NO_JOIN_COLUMNS
See Also:
Constant Field Values

HJ_HASH_KEY_COLUMNS

public static final int HJ_HASH_KEY_COLUMNS
See Also:
Constant Field Values

CALLING_ON_JOIN_NODE

public static final int CALLING_ON_JOIN_NODE
See Also:
Constant Field Values

CONSIDERING_JOIN_STRATEGY

public static final int CONSIDERING_JOIN_STRATEGY
See Also:
Constant Field Values

REMEMBERING_BEST_ACCESS_PATH

public static final int REMEMBERING_BEST_ACCESS_PATH
See Also:
Constant Field Values

NO_MORE_CONGLOMERATES

public static final int NO_MORE_CONGLOMERATES
See Also:
Constant Field Values

CONSIDERING_CONGLOMERATE

public static final int CONSIDERING_CONGLOMERATE
See Also:
Constant Field Values

SCANNING_HEAP_FULL_MATCH_ON_UNIQUE_KEY

public static final int SCANNING_HEAP_FULL_MATCH_ON_UNIQUE_KEY
See Also:
Constant Field Values

ADDING_UNORDERED_OPTIMIZABLE

public static final int ADDING_UNORDERED_OPTIMIZABLE
See Also:
Constant Field Values

CHANGING_ACCESS_PATH_FOR_TABLE

public static final int CHANGING_ACCESS_PATH_FOR_TABLE
See Also:
Constant Field Values

TABLE_LOCK_NO_START_STOP

public static final int TABLE_LOCK_NO_START_STOP
See Also:
Constant Field Values

NON_COVERING_INDEX_COST

public static final int NON_COVERING_INDEX_COST
See Also:
Constant Field Values

ROW_LOCK_ALL_CONSTANT_START_STOP

public static final int ROW_LOCK_ALL_CONSTANT_START_STOP
See Also:
Constant Field Values

ESTIMATING_COST_OF_CONGLOMERATE

public static final int ESTIMATING_COST_OF_CONGLOMERATE
See Also:
Constant Field Values

LOOKING_FOR_SPECIFIED_INDEX

public static final int LOOKING_FOR_SPECIFIED_INDEX
See Also:
Constant Field Values

MATCH_SINGLE_ROW_COST

public static final int MATCH_SINGLE_ROW_COST
See Also:
Constant Field Values

COST_INCLUDING_EXTRA_1ST_COL_SELECTIVITY

public static final int COST_INCLUDING_EXTRA_1ST_COL_SELECTIVITY
See Also:
Constant Field Values

CALLING_NEXT_ACCESS_PATH

public static final int CALLING_NEXT_ACCESS_PATH
See Also:
Constant Field Values

TABLE_LOCK_OVER_THRESHOLD

public static final int TABLE_LOCK_OVER_THRESHOLD
See Also:
Constant Field Values

ROW_LOCK_UNDER_THRESHOLD

public static final int ROW_LOCK_UNDER_THRESHOLD
See Also:
Constant Field Values

COST_INCLUDING_EXTRA_START_STOP

public static final int COST_INCLUDING_EXTRA_START_STOP
See Also:
Constant Field Values

COST_INCLUDING_EXTRA_QUALIFIER_SELECTIVITY

public static final int COST_INCLUDING_EXTRA_QUALIFIER_SELECTIVITY
See Also:
Constant Field Values

COST_INCLUDING_EXTRA_NONQUALIFIER_SELECTIVITY

public static final int COST_INCLUDING_EXTRA_NONQUALIFIER_SELECTIVITY
See Also:
Constant Field Values

COST_OF_NONCOVERING_INDEX

public static final int COST_OF_NONCOVERING_INDEX
See Also:
Constant Field Values

REMEMBERING_JOIN_STRATEGY

public static final int REMEMBERING_JOIN_STRATEGY
See Also:
Constant Field Values

REMEMBERING_BEST_ACCESS_PATH_SUBSTRING

public static final int REMEMBERING_BEST_ACCESS_PATH_SUBSTRING
See Also:
Constant Field Values

REMEMBERING_BEST_SORT_AVOIDANCE_ACCESS_PATH_SUBSTRING

public static final int REMEMBERING_BEST_SORT_AVOIDANCE_ACCESS_PATH_SUBSTRING
See Also:
Constant Field Values

REMEMBERING_BEST_UNKNOWN_ACCESS_PATH_SUBSTRING

public static final int REMEMBERING_BEST_UNKNOWN_ACCESS_PATH_SUBSTRING
See Also:
Constant Field Values

COST_OF_CONGLOMERATE_SCAN1

public static final int COST_OF_CONGLOMERATE_SCAN1
See Also:
Constant Field Values

COST_OF_CONGLOMERATE_SCAN2

public static final int COST_OF_CONGLOMERATE_SCAN2
See Also:
Constant Field Values

COST_OF_CONGLOMERATE_SCAN3

public static final int COST_OF_CONGLOMERATE_SCAN3
See Also:
Constant Field Values

COST_OF_CONGLOMERATE_SCAN4

public static final int COST_OF_CONGLOMERATE_SCAN4
See Also:
Constant Field Values

COST_OF_CONGLOMERATE_SCAN5

public static final int COST_OF_CONGLOMERATE_SCAN5
See Also:
Constant Field Values

COST_OF_CONGLOMERATE_SCAN6

public static final int COST_OF_CONGLOMERATE_SCAN6
See Also:
Constant Field Values

COST_OF_CONGLOMERATE_SCAN7

public static final int COST_OF_CONGLOMERATE_SCAN7
See Also:
Constant Field Values

COST_INCLUDING_COMPOSITE_SEL_FROM_STATS

public static final int COST_INCLUDING_COMPOSITE_SEL_FROM_STATS
See Also:
Constant Field Values

COMPOSITE_SEL_FROM_STATS

public static final int COMPOSITE_SEL_FROM_STATS
See Also:
Constant Field Values

COST_INCLUDING_STATS_FOR_INDEX

public static final int COST_INCLUDING_STATS_FOR_INDEX
See Also:
Constant Field Values
Method Detail

getNextPermutation

public boolean getNextPermutation()
                           throws StandardException
Iterate through the permutations, returning false when the permutations are exhausted. NOTE - Implementers are responsible for hiding tree pruning of permutations behind this method call.

Returns:
boolean True - An optimizable permutation remains. False - Permutations are exhausted.
Throws:
StandardException - Thrown on error

getNextDecoratedPermutation

public boolean getNextDecoratedPermutation()
                                    throws StandardException
Iterate through the "decorated permutations", returning false when they are exhausted. NOTE - Implementers are responsible for hiding tree pruning of access methods behind this method call.

Returns:
boolean True - An optimizable decorated permutation remains. False - Decorated permutations are exhausted.
Throws:
StandardException - Thrown on error

costPermutation

public void costPermutation()
                     throws StandardException
Cost the current permutation. Caller is responsible for pushing all predicates which can be evaluated prior to costing.

Returns:
Nothing.
Throws:
StandardException - Thrown on error

costOptimizable

public void costOptimizable(Optimizable optimizable,
                            TableDescriptor td,
                            ConglomerateDescriptor cd,
                            OptimizablePredicateList predList,
                            CostEstimate outerCost)
                     throws StandardException
Cost the current Optimizable with the specified OPL. Caller is responsible for pushing all predicates which can be evaluated prior to costing.

Parameters:
optimizable - The Optimizable
td - TableDescriptor of the Optimizable
cd - The ConglomerateDescriptor for the conglom to cost (This should change to an object to represent access paths, but for now this is OK).
predList - The OptimizablePredicateList to apply
outerCost - The cost of the tables outer to the one being optimizer - tells how many outer rows there are.
Returns:
Nothing.
Throws:
StandardException - Thrown on error

considerCost

public void considerCost(Optimizable optimizable,
                         OptimizablePredicateList predList,
                         CostEstimate estimatedCost,
                         CostEstimate outerCost)
                  throws StandardException
Consider the cost of the given optimizable. This method is like costOptimizable, above, but it is used when the Optimizable does not need help from the optimizer in costing the Optimizable (in practice, all Optimizables except FromBaseTable use this method. Caller is responsible for pushing all predicates which can be evaluated prior to costing.

Parameters:
optimizable - The Optimizable
predList - The OptimizablePredicateList to apply
estimatedCost - The estimated cost of the given optimizable
outerCost - The cost of the tables outer to the one being optimizer - tells how many outer rows there are.
Returns:
Nothing.
Throws:
StandardException - Thrown on error

getDataDictionary

public DataDictionary getDataDictionary()
Return the DataDictionary that the Optimizer is using. This is useful when an Optimizable needs to call optimize() on a child ResultSetNode.

Returns:
DataDictionary DataDictionary that the Optimizer is using.

modifyAccessPaths

public void modifyAccessPaths()
                       throws StandardException
Modify the access path for each Optimizable, as necessary. This includes things like adding result sets to translate from index rows to base rows.

Throws:
StandardException - Thrown on error

newCostEstimate

public CostEstimate newCostEstimate()
Get a new CostEstimate object


getOptimizedCost

public CostEstimate getOptimizedCost()
Get the estimated cost of the optimized query


setOuterRows

public void setOuterRows(double outerRowCount)
Set the estimated number of outer rows - good for optimizing nested optimizables like subqueries and join nodes.


getNumberOfJoinStrategies

public int getNumberOfJoinStrategies()
Get the number of join strategies supported by this optimizer.


tableLockThreshold

public int tableLockThreshold()
Get the maximum number of estimated rows touched in a table before we decide to open the table with table locking (as opposed to row locking.


getJoinStrategy

public JoinStrategy getJoinStrategy(int whichStrategy)
Gets a join strategy by number (zero-based).


getJoinStrategy

public JoinStrategy getJoinStrategy(java.lang.String whichStrategy)
Gets a join strategy by name. Returns null if not found. The look-up is case-insensitive.


trace

public void trace(int traceFlag,
                  int intParam1,
                  int intParam2,
                  double doubleParam,
                  java.lang.Object objectParam1)
Optimizer trace.


getLevel

public int getLevel()
Get the level of this optimizer.

Returns:
The level of this optimizer.

uniqueJoinWithOuterTable

public double uniqueJoinWithOuterTable(OptimizablePredicateList predList)
                                throws StandardException
Tells whether any of the tables outer to the current one has a uniqueness condition on the given predicate list, and if so, how many times each unique key can be seen by the current table.

Parameters:
predList - The predicate list to check
Returns:
<= 0 means there is no uniqueness condition > 0 means there is a uniqueness condition on an outer table, and the return value is the reciprocal of the maximum number of times the optimizer estimates that each unique key will be returned. For example, 0.5 means the optimizer thinks each distinct join key will be returned at most twice.
Throws:
StandardException - Thrown on error

useStatistics

public boolean useStatistics()
If statistics should be considered by the optimizer while optimizing a query. The user may disable the use of statistics by setting the property derby.optimizer.useStatistics or by using the property useStatistics in a query.

See Also:
USE_STATISTICS

getMaxMemoryPerTable

public int getMaxMemoryPerTable()
Returns:
the maximum number of bytes to be used per table.

Built on Mon 2007-06-04 09:58:47+0400, from revision ???

Apache Derby V10.1 Engine Documentation - Copyright © 1997,2005 The Apache Software Foundation or its licensors, as applicable.