Overview of Data Warehousing with Materialized Views

Typically, data flows from one or more online transaction processing (OLTP) database
into a data warehouse on a monthly, weekly, or daily basis. The data is normally
processed in a staging file before being added to the data warehouse. Data
warehouses commonly range in size from tens of gigabytes to a few terabytes. Usually,
the vast majority of the data is stored in a few very large fact tables.

One technique employed in data warehouses to improve performance is the creation
of summaries. Summaries are special types of aggregate views that improve query
execution times by precalculating expensive joins and aggregation operations prior to
execution and storing the results in a table in the database. For example, you can
create a summary table to contain the sums of sales by region and by product.

The summaries or aggregates that are referred to in this book and in literature on data warehousing are created in Oracle Database using a schema object called a
materialized view. Materialized views can perform a number of roles, such as
improving query performance or providing replicated data.

In the past, organizations using summaries spent a significant amount of time and
effort creating summaries manually, identifying which summaries to create, indexing
the summaries, updating them, and advising their users on which ones to use. The
introduction of summary management eased the workload of the database
administrator and meant the user no longer needed to be aware of the summaries that
had been defined.
The database administrator creates one or more materialized views,
which are the equivalent of a summary. The end user queries the tables and views at
the detail data level. The query rewrite mechanism in the Oracle server automatically
rewrites the SQL query to use the summary tables. This mechanism reduces response
time for returning results from the query. Materialized views within the data
warehouse are transparent to the end user or to the database application.

Although materialized views are usually accessed through the query rewrite
mechanism, an end user or database application can construct queries that directly
access the materialized views. However, serious consideration should be given to
whether users should be allowed to do this because any change to the materialized
views will affect the queries that reference them.

Materialized Views for Data Warehouses
In data warehouses, you can use materialized views to precompute and store
aggregated data such as the sum of sales. Materialized views in these environments
are often referred to as summaries, because they store summarized data. They can also
be used to precompute joins with or without aggregations. A materialized view
eliminates the overhead associated with expensive joins and aggregations for a large
or important class of queries.

Materialized Views for Distributed Computing
In distributed environments, you can use materialized views to replicate data at
distributed sites and to synchronize updates done at those sites with conflict
resolution methods. These replica materialized views provide local access to data that otherwise would have to be accessed from remote sites. Materialized views are also useful in remote data marts.

Materialized Views for Mobile Computing
You can also use materialized views to download a subset of data from central servers
to mobile clients, with periodic refreshes and updates between clients and the central
servers.

Posted in: Materialized Views