ASA SQL Reference
SQL Functions
Alphabetical list of functions
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. This is optional.
relation-string The comparison operator used for the comparison, enclosed in single quotes. The default is equality (=).
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, Adaptive Server 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 Adaptive Server 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/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Not supported by Adaptive Server Enterprise.
ESTIMATE function [Miscellaneous]
INDEX_ESTIMATE function [Miscellaneous]
The following statement returns the value Index, which means that the query optimizer probed an index to estimate the selectivity.
SELECT FIRST ESTIMATE_SOURCE( emp_id, 200, '>' ) FROM employee