A materialized view is a very useful object. 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 ToolTip:
There are several different areas where a materialized view is beneficial, as depicted in the following table:
Improves performance by providing pre-computed aggregate values that are typically required in the dimensions of a data warehouse.
Used for snapshot replication to distributed databases.
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
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.