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

SQL Anywhere 12.0.0 (Français) » SQL Anywhere Server - SQL Usage » Query optimization and execution » How the optimizer works


Optimizer estimates and column statistics

The optimizer chooses a strategy for processing a statement based on column statistics stored in the database and on heuristics (educated guesses). For each access plan considered by the optimizer, an estimated result size (number of rows) must be computed. For example, for each join method or index access based on the selectivity estimations of the predicates used in the query, an estimated result size is calculated. The estimated result sizes are used to compute the estimated disk access and CPU cost for each operator such as a join method, a group by method, or a sequential scan, used in the plan. Column statistics are the primary data used by the optimizer to compute selectivity estimation of predicates. Therefore, they are vital to estimating correctly the cost of an access plan.

If column statistics become stale, or are missing, performance can degrade since inaccurate statistics may result in an inefficient execution plan. If you suspect that poor performance is due to inaccurate column statistics, you should recreate them. See Updating column statistics to improve optimizer performance.

Selectivity estimate sources
How the optimizer uses column statistics
How the optimizer uses heuristics
How the optimizer uses procedure statistics
Updating column statistics to improve optimizer performance