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.

If a query explicitly references a materialized view, the view is used to process the query, regardless of freshness of the data in the view. 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. Adjust the interval specified for the event or trigger responsible for refreshing the view.

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.