ESTIMATE_SOURCE function [Miscellaneous]

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

Remarks

If value is NULL then the relation strings '=' and '!=' are interpreted as the IS NULL and IS NOT NULL conditions, respectively.

Return value

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.

See also
Standards and compatibility

  • SQL/2003   Vendor extension.

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( EmployeeID, 200, '>' )
   FROM Employees
   ORDER BY 1;
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0