Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.1 » SQL Anywhere Server - SQL Usage » Query and modify data » Queries » Advanced: Query optimization » How the optimizer works


Selectivity estimate sources

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.

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

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

 See also