|
![ESTIMATE function [Miscellaneous]](gif/backward.gif)
Provides the source for selectivity estimates used by the query optimizer.
ESTIMATE_SOURCE(
column-name
[, value
[, relation-string ] ]
)
column-name The name of the column that is being investigated.
value The value to which the column is compared. The default is NULL.
relation-string The comparison operator used for the comparison, enclosed in single quotes. Possible values for this parameter are: =, >, <, >= , <=, <>, !=, !<, and !>. The default is =.
If value is NULL then the relation strings '=' and '!=' are interpreted as the IS NULL and IS NOT NULL conditions, respectively.
The source of the selectivity estimate can be one of the following:
Statistics is used as the source when you have specified a value, and there is a stored statistic available that estimates the average selectivity of the value in the column. The statistic is available only when the selectivity of the value is a significant enough number that it is stored in the statistics. Currently, a value is deemed significant if it occurs in at least 1% of the rows.
Column is similar to Statistics, except that the selectivity of the value occurs in less than 1% of the rows. In this case, the selectivity that is used is the average of all values that have been stored in the statistics that occur in less than 1% of rows.
Guess is returned when there is no relevant index to use, and no statistics have been collected for the column. In this case, built-in guesses are used.
Column-column is returned when the estimate that is used is the selectivity of a join. In this case, the estimate is calculated as the number of rows in the joined result set divided by the number of rows in the Cartesian product of the two tables.
Index is used as the source when there are no statistics available to estimate the selectivity, but there is an index which can be probed to estimate selectivity.
User is returned when there is a user supplied estimate, and the user_estimates database option is not set to Disabled.
For more information, see user_estimates option [database].
Computed is returned when statistics are computed by the optimizer based on other information. For example, SQL Anywhere does not maintain statistics on multiple columns, so if you want an estimate on a multiple column equation, such as x=5 and y=10, and there are statistics on the columns x and y, then the optimizer creates an estimate by multiplying the estimated selectivity for each column.
Always is used when the test is by definition true. For example, if the value is 1=1.
Combined is used when the optimizer uses more than one of the above sources, and combines them.
Bounded can qualify one of the other sources. This indicates that SQL Anywhere has placed an upper and/or lower bound on the estimate. The optimizer does this to keep estimates within logical bounds. For example, it ensures that an estimate is not greater than 100%, or that the selectivity is not less than one row.
SQL/2003 Vendor extension.
The following statement returns the value Index, which means that the query optimizer probed an index to estimate the selectivity.
SELECT FIRST ESTIMATE_SOURCE( EmployeeID, 200, '>' ) FROM Employees;