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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Working with Database Objects » Working with views » Working with materialized views

Enabling and disabling optimizer use of a materialized view Next Page

Setting the optimizer staleness threshold for materialized views

Data in a materialized view becomes stale when the data changes in the tables referenced by the materialized view. The materialized_view_optimization database option allows you to configure a staleness threshold beyond which the optimizer should no longer consider using it when processing queries. However, if a query references a materialized view directly, the view will be used to process the query, regardless of staleness. Also, the SELECT statement of a query may contain an OPTION clause that overrides the setting of the materialized_view_optimization database option.

If you notice that the materialized view is not considered by the optimizer, it may be due to staleness. You should adjust accordingly the interval specified for the event responsible for refreshing the view.


When snapshot isolation is in use, the optimizer avoids using a materialized view if it was refreshed after the start of the snapshot for a transaction.

For information on how to use the materialized_view_optimization database option, see materialized_view_optimization option [database].

For information on how to use the OPTION clause of the SELECT statement to override the materialized_view_optimization database option, see SELECT statement.

For information on using events and triggers, see Automating Tasks Using Schedules and Events.

For information on determining whether the materialized view has been considered by optimizer, see Reading execution plans and Monitor query performance.