Explicit selectivity estimates

ASA SQL Reference
SQL Language Elements
Search conditions

Explicit selectivity estimates

Adaptive Server Anywhere uses statistical information to determine the most efficient strategy for executing each statement. Adaptive Server Anywhere automatically gathers and updates these statistics. These statistics are stored permanently in the database in the system table SYSCOLSTAT. Statistics gathered while processing one statement are available when searching for efficient ways to execute subsequent statements.

Occasionally, the statistics may become inaccurate or relevant statistics may be unavailable. This condition is most likely to arise when few queries have been executed since a large amount of data was added, updated, or deleted.

In this situation, you may want to execute CREATE STATISTICS or DROP STATISTICS.

In unusual circumstances, however, these measures may prove ineffective. In such cases, you can sometimes improve performance by supplying explicit selectivity estimates.

For each table in a potential execution plan, the optimizer must estimate the number of rows that will be part of the result set. If you know that a condition has a success rate that differs from the optimizer's estimate, you can explicitly supply a user estimate in the search condition.

The estimate is a percentage. It can be a positive integer or can contain fractional values.

Caution: Whenever possible, avoid supplying explicit estimates in statements that are to be used on an ongoing basis. Should the data change, the explicit estimate may become inaccurate and may force the optimizer to select poor plans.

You can disable user estimates by setting the database option USER_ESTIMATES to OFF. The default value for USER_ESTIMATES is OVERRIDE-MAGIC, which means that user-supplied selectivity estimates are used only when the optimizer would use a MAGIC (default) selectivity value for the condition. The optimizer uses MAGIC values as a last resort when it is unable to accurately predict the selectivity of a predicate.

For more information about disabling user-defined selectivity estimates, see USER_ESTIMATES option [database].

For more information about statistics, see Optimizer estimates.

Examples

The following query provides an estimate that one percent of the ship_date values will be later than 2001/06/30:
```
SELECT  ship_date
    FROM  sales_order_items
WHERE ( ship_date > '2001/06/30', 1 )
ORDER BY ship_date DESC
```
The following query estimates that half a percent of the rows will satisfy the condition:
```
SELECT *
    FROM customer c, sales_order o
WHERE (c.id = o.cust_id, 0.5)
```

Fractional values enable more accurate user estimates for joins, particularly for large tables.

Standards and compatibility

Adaptive Server Enterprise does not support explicit estimates.