Oracle 23c Materialized Views – Module Summary

This conclusion lesson ties together everything you have seen about materialized views in Oracle 23c. Across the workflow pages in this module, you learned when materialized views are appropriate, how to create and index them, how Oracle handles invalidation and refresh, how query rewrite works, and how to manage and tune materialized views using modern packages such as DBMS_MVIEW and DBMS_STATS.

1. Introduction: Why use materialized views?

The introductory lesson (Intro to Oracle Materialized Views) explained the motivation for materialized views. You compared traditional queries against large fact tables with precomputed results stored in a materialized view. The key idea is that materialized views can dramatically reduce response time for reporting and analytic workloads, especially in data warehouse, remote, or distributed environments, while allowing applications to keep using their existing SQL.

2. What a materialized view is and how it behaves

The lesson Oracle Materialized View Concepts described the architecture of a materialized view: how it is based on a defining query, how it stores the query results on disk, and how Oracle keeps it synchronized with the base tables. You examined complete, fast, and force refresh modes and saw how refresh policies (on demand, on commit, or scheduled) influence both performance and data freshness.

3. Syntax and creation of materialized views

In Materialized View Syntax and Creation you broke down the CREATE MATERIALIZED VIEW statement. The lesson reviewed key clauses such as BUILD IMMEDIATE vs BUILD DEFERRED, REFRESH FAST vs REFRESH COMPLETE, ENABLE QUERY REWRITE, and partitioning options. You learned how to map business requirements (refresh windows, data latency, storage limits) into specific options in the DDL.

4. Summary data and joins in materialized views

The lesson Summary Data and Joins showed how materialized views can store both aggregated data and pre-joined result sets. You saw examples where sales facts are joined with dimension tables and grouped by time, geography, or product. By pushing joins and aggregations into the materialized view definition, you offload expensive work from user queries and make it easier for Oracle to rewrite analytic SQL to use the precomputed results.

5. Indexing strategies on materialized views

In Indexing Materialized Views you focused on how indexes interact with materialized views. You compared B-tree indexes with bitmap indexes, especially in data warehouse scenarios with low-cardinality columns. The lesson emphasized choosing index keys that support typical query predicates and grouping columns, and it reminded you to balance query speed with refresh overhead and storage.

6. Invalidation, refresh, and staleness

The lesson Handling Invalidation explained what happens when base-table data changes. You examined how Oracle marks a materialized view as stale, how to interpret data dictionary columns such as STALENESS and LAST_REFRESH_DATE, and when a refresh is required. You also saw how materialized view logs support fast refresh by tracking changed rows on the base tables.

7. Conditions for query rewrite

In Query Rewriting Conditions you learned what must be true for Oracle to rewrite a user query to use a materialized view. The lesson covered initialization parameters such as QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY, the role of up-to-date optimizer statistics, and structural requirements such as matching joins, filters, and aggregation levels. You also saw how DBMS_MVIEW.EXPLAIN_REWRITE can help diagnose why a particular query is not being rewritten.

8. Managing, tuning, and automating materialized views

The lesson Managing Materialized Views brought everything together from an administrative perspective. You used DBMS_MVIEW to refresh and analyze materialized views, DBMS_STATS to maintain statistics, and SQL Access Advisor to receive recommendations for new materialized views based on workload. You also saw how Automatic Materialized Views in Oracle 23c/23ai build on these concepts to identify and manage candidate views automatically.

9. Putting it all together

This conclusion page is the final step in the workflow. At this point you should be able to identify good candidates for materialized views, create them with appropriate refresh and query rewrite options, index them effectively, monitor their status, and use modern tuning tools to evolve your design over time. If your environment includes data warehousing, reporting, or remote replication, materialized views can deliver significant performance benefits with relatively little application change.

Glossary recap

Materialized Views – Quiz