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

materialized_view_optimization option

Controls how materialized views are used by the optimizer to answer queries efficiently.

Allowed values

Disabled, Fresh, Stale, N { Minute[s] | Hour[s] | Day[s] | Week[s] | Month[s] }



  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

The materialized_view_optimization option lets you specify the circumstances under which the optimizer can use stale materialized views.

Data in a materialized view becomes stale when data in any of the base tables referenced by the materialized view is updated. Consider the acceptable degree of data staleness when deciding the refresh frequency for the materialized view, and the time it takes to refresh the view, since the view is not available for querying during the refresh process. Also consider whether it is acceptable for the database server to return results that may not reflect the current state of the database. You can choose from the following settings for this option:

  • Disabled

    Do not use materialized views for query optimization.

  • Fresh

    Use a materialized view only if it is fresh (data in underlying tables has not been modified since the view was last refreshed).

  • Stale

    Use materialized views even if they are stale. This value is the default setting.

  • N { Minute[s] | Hour[s] | Day[s] | Week[s] | Month[s] }

    Use fresh and stale materialized views, as long as the stale materialized views have been refreshed within the specified time period. Values specified in minutes must be less than 231 minutes. The database server treats a week as 7 days and a month as 30 days.

When a query directly references a materialized view, the view is used regardless of staleness; the materialized_view_optimization option has no effect in this case.