ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

ESTIMATE_SOURCE function [Miscellaneous]

Function 

Provides the source for selectivity estimates used by the query optimizer.

Syntax 

ESTIMATE_SOURCE (
column-name
[, value  [ , relation-string ] ] )

Parameters 

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 (=).

• 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.

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

• Sybase    Not supported by Adaptive Server Enterprise.

See also 

ESTIMATE function [Miscellaneous]

INDEX_ESTIMATE function [Miscellaneous]

Example 

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