What is a Materialized View?

Materialized views are still available and fully supported in Oracle 12c. They continue to be a powerful feature for enhancing query performance, especially in data warehousing and decision support systems.
Here's a summary of key points about materialized views in Oracle 12c:

• Purpose:
  • Pre-compute and store results of expensive joins and aggregations.
  • Improve query performance by reducing the need for repeated calculations.
• How they work:
  • Created using `CREATE MATERIALIZED VIEW` syntax.
  • Stored as physical tables, refreshed periodically to reflect changes in base tables.
  • Query optimizer can transparently rewrite queries to use materialized views when applicable.
• Key features in Oracle 12c:
  • Real-time materialized views (12.2): Allow statement-level wind-forward of stale materialized views, making them appear fresh to the current statement. This minimizes the impact of refresh lag on query results.
  • Out-of-place refresh (12.1): Improves refresh performance and availability by using external tables during the refresh process, reducing the impact on the materialized view itself.
  • Advanced refresh options: Support for various refresh methods, including fast refresh, complete refresh, PCT refresh (for partitioned tables), and force refresh.
  • Query rewrite: Optimizer can automatically rewrite queries to leverage materialized views, even if the query doesn't directly reference them.

In summary, materialized views are a valuable tool for optimizing query performance in Oracle 12c. Their continued availability and enhancements in 12c demonstrate their importance for data warehousing and decision support workloads.
The basic function of a materialized view is to store pre-computed aggregate values or joins. The materialized view can be selected by the cost-based query optimizer to provide rapid retrieval of data without having to perform resource-intensive aggregate functions or joins. The summary information in a materialized view is loaded into the view from one or more base tables of view. The view can then be queried, either explicitly or automatically. The flow of data through the view is illustrated in the following diagram:

There are several different areas where a materialized view is beneficial, as depicted in the following table:

Area	Benefit
Data warehousing	Improves performance by providing pre-computed aggregate values that are typically required in the dimensions of a data warehouse.
Distributed databases	Used for snapshot replication to distributed databases.
Mobile computing	Used for replication and aggregation transfer to mobile or remote clients.

Materialized views also provide automatic refresh capabilities, which are useful for data warehouse maintenance and replication in distributed or remote implementations.

Materialized views are copies (also known as replicas) of data, based upon queries. In its simplest form, a materialized view can be thought of as a table created by a command such as the following:

create table LOCAL_BOOKSHELF
as
select * from BOOKSHELF@REMOTE_CONNECT;

In this example, a table named LOCAL_BOOKSHELF is created in the local database and is populated with data from a remote database (defined by the database link named REMOTE_CONNECT). Once the LOCAL_BOOKSHELF table is created, though, its data may immediately become out of sync with the master table (BOOKSHELF@REMOTE_CONNECT). Also, LOCAL_BOOKSHELF may be updated by local users, further complicating its synchronization with the master table. Despite these synchronization problems, there are benefits to replicating data in this way. Creating local copies of remote data may improve the performance of distributed queries, particularly if the master table’s data does not change frequently. You may also use the local table creation process to restrict the rows returned, restrict the columns returned, or generate new columns (such as by applying functions to selected values). This is a common strategy for decision-support environments, in which complex queries are used to periodically “roll up” data into summary tables for use during analyses.
Materialized views automate the data replication and refresh processes. When materialized views are created, a refresh interval is established to schedule refreshes of replicated data. Local updates can be prevented, and transaction-based refreshes can be used. Transaction-based refreshes, available for many types of materialized views, send from the master database only those rows that have changed for the materialized view. This capability, may significantly improve the performance of your refreshes. In the next lesson, you will learn the syntax for creating materialized views.