Four ways to get the most out of materialized views

While materialized views—especially with query rewrite—are a powerful way to speed up big queries, don’t assume that you’re getting all the performance you could be getting just because you’ve set them up. In some cases, you may not get any performance gain for your efforts! Here are four easily overlooked points to make sure that your investment in materialized views is paying off:

• Don’t forget to create indexes on large materialized views. These indexes are created in exactly the same way as for tables.
• For very large materialized views, consider partitioning; you do this the same way as for tables.
• When initially trying the query rewrite feature, consider using a query with the identical SQL that’s used to define the materialized view. This ensures that query rewrite won’t be rejected because of some minor difference in the SQL code.
• Consider using the initialization parameter QUERY_REWRITE_INTEGRITY. If you set this parameter to STALE_TOLERATED instead, the optimizer may invoke query rewrite without considering whether the data in the materialized view is up to date. Alternatively, setting the parameter to ENFORCED prevents query rewrite if the materialized view has stale data.