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

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Alphabetical list of database options

user_estimates option

Controls whether user selectivity estimates in query predicates are respected or ignored by the query optimizer.

Allowed values

Enabled, Disabled, Override-Magic



  PUBLIC role For current user For other users
Allowed to set permanently? Yes, with SET ANY PUBLIC OPTION Yes Yes, with SET ANY PUBLIC OPTION
Allowed to set temporarily? Yes, with SET ANY PUBLIC OPTION Yes (current connection only) No

You can specify user selectivity estimates to improve the optimizer's performance when the database server is unable to accurately predict the selectivity of a predicate. However, user selectivity estimates should be used only in appropriate circumstances. For example, it may be useful to supply a selectivity estimate for a predicate that involves one or more functions if the Override-Magic selectivity estimate used by the optimizer is significantly different from the actual selectivity.

If you have used selectivity estimates that are inaccurate as a workaround to performance problems where the software-selected access plan was poor, set this option to Disabled. The database server may not select an optimal plan if you use inaccurate estimates.

When a user selectivity estimate is supplied with a predicate, the estimate is respected or ignored based on the setting of this option. The following values are accepted:

  • Enabled

    All user-supplied selectivity estimates are respected. You can also use On to turn on this option.

  • Override-Magic

    A user selectivity estimate is respected and used only if the optimizer would otherwise choose to use its last-resort, heuristic value (also called the magic value).

  • Disabled

    All user estimates are ignored and magic values are used when no other estimate data is available. You can also use Off to turn off this option.

You can override any temporary or PUBLIC settings for this option within individual INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements by including an OPTION clause in the statement.