This content has been marked as final. Show 1 reply
If the query is simple, would it be possible to let the materialized view do an incremental refresh instead (this would require that a materialized view log be created on the base table(s))? If so, that would definitely make refreshing the materialized view more efficient.
Would you gain any benefit from enabling query rewrite in the database so that queries against the base table could be rewritten to use the materialized view? Ideally, you can use materialized views in this way to optimize many different queries against the base tables and simplify the process of building new queries by letting people query the base table and then letting the optimizer figure out if the materialized view can be used to improve performance. That lets you treat adding new materialized views much like you would treat adding new indexes-- a way to transparently increase the performance of a number of different queries without touching the queries themselves.