Data in a materialized view becomes stale when the data changes in the tables referenced by the materialized view. If you notice that the materialized view is not considered by the optimizer, it may be due to staleness. You can adjust the staleness threshold for materialized views using the materialized_view_optimization database option.

You can also adjust the interval specified for the event or trigger that is responsible for refreshing the view.

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 statements such as SELECT, UPDATE, and INSERT can be used to override the setting of the materialized_view_optimization database option, forcing the use of a materialized 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.

 See also