For any predicate, the optimizer can use any of the following source for selectivity estimates. The chosen source is indicated in the graphical and long plan for the query.
Statistics The optimizer can use stored column statistics to calculate selectivity estimates. If constants are used in the predicate, the stored statistics are available only when the selectivity of a constant is a significant enough number that it is stored in the statistics.
For example, the predicate
EmployeeID > 100 can use column statistics as the selectivity estimate source if the statistics for the EmployeeID column exists.
The optimizer can use referential integrity constraints, unique constraints, or join histograms to compute selectivity
estimates. Join histograms are computed for a predicate of the form
T.X=R.X from the available statistics of the T.X and R.X columns.
Column-column In the case of a join where there are no referential integrity constraints, unique constraints, or join histograms available to use as selectivity sources, the optimizer can use, as a selectivity source, the estimated number of rows in the joined result set divided by the number of rows in the Cartesian product of the two tables.
Column The optimizer can use the average of all values that have been stored in the column statistics.
For example, the selectivity of the predicate
DepartmentName = expression can be computed using the average if expression is not a constant.
Index The optimizer can probe indexes to compute selectivity estimates. In general, an index is used for selectivity estimates if no other sources of selectivity estimates, for example column statistics, can be used.
For example, for the predicate
DepartmentName = 'Sales', the optimizer can use an index defined on the column DepartmentName to estimate the number of rows having the value Sales.
User The optimizer can use user-supplied selectivity estimates, provided the user_estimates database option is not set to Disabled.
Guess The optimizer can resort to best guessing to calculate selectivity estimates when there is no relevant index to use, no statistics have been collected for the referenced columns, or the predicate is a complex predicate. In this case, built-in guesses are defined for each type of predicate.
Computed For example, a very complex predicate may have the selectivity estimate set to 100% and the selectivity source set to Computed if the selectivity estimate was computed, for example, by multiplying or adding the selectivities.
If a predicate is always true, the selectivity source is 'Always'. For example, the predicate
1=1 is always true.
Combined If the selectivity estimate is computed by combining more than one of the sources above, the selectivity source is 'Combined'.
Bounded When SQL Anywhere has placed an upper and/or lower bound on the selectivity estimate, the selectivity source is 'Bounded'. For example, bounds are sets to ensure that an estimate is not greater than 100%, or that the selectivity is not less than 0%.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|