OCM 11g Preparation - Manage Materialized Views to improve rewrite and refresh performance

Materialized views are, along with indexes, the most important thing in a DB to improve performance of complex joins in tables that are not updated so frequently. Having a good knowledge of it is very important, so, finish this topic only when you understand very well it's usage.

Most important thing here, as the topic says is:

  • Enable Query REWRITE
  • Enable FAST refresh

And to do that you need to understand:

  • How to create MVIEW and MVIEW LOGs;
  • Refresh types (Complete/Fast/Force/Never);
  • QUERY_REWRITE_INTEGRITY parameter;
  • Requirements for Using Materialized Views with Aggregates;
  • Creating prebuilt mviews;

Path to Documentation:

Oracle Database Data Warehousing Guide -> Basic/Advanced Materialized Views
Oracle Database Data Warehousing Guide -> Basic/Advanced Query Rewrite
Oracle Database SQL Language Reference -> CREATE MATERIALIZED VIEW (LOG)


Click here to go back to the Main OCM 11g Preparation page.

Have you enjoyed? Please leave a comment or give a 👍!

Leave a Reply

Your email address will not be published.