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. The materialized_view_optimization database option does not impact how often materialized views are refreshed.

Also, if a query explicitly references a materialized view, the view is still used to process the query, regardless of whether the view is considered stale. As well, the OPTION clause of a SELECT statement can be used to override the setting of the materialized_view_optimization database option, forcing the use of the materialized view. See SELECT statement.

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 or trigger responsible for refreshing the view.

Note

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 about how to use the materialized_view_optimization database option, see materialized_view_optimization option [database].

For information about using events and triggers, see Automating tasks using schedules and events.

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