Oracle Tips by Burleson

Chapter 2 Physical Entity Design for Oracle

In other words, the data is replicated, and Oracle requires you to specify a schedule for periodic updates. Updates are accomplished by way of a refresh interval, which can range from instantaneous rebuilding of the materialized view to a hot refresh that occurs weekly.

Oracle materialized views are quite complex in nature and require a significant understanding to be used effectively. In this article, I covered the required set-up methods and the steps for creating materialized views and appropriate refresh intervals.

In the world of database architecture, the need to dynamically create complex objects conflicts with the demand for sub-second response time. Oracle's answer to this dilemma is the materialized view. Database designers can use materialized views to pre-join tables, presort solution sets, and pre-summarize complex data warehouse information. Because this work is completed in advance, it gives end users the illusion of instantaneous response time. Materialized views are especially useful for Oracle data warehouses, where cross-tabulations often take hours to perform. This article explores the internals of materialized views and demonstrates how to pre-compute complex aggregates—having Oracle dynamically rewrite SQL to reference pre-computed aggregate information. This is the first of two articles concentrating on Oracle materialized views.

Prior to Materialized Views, database administrators using summaries spent a significant amount of time manually identifying which ones to create and then creating, indexing, and updating them, and advising their users which ones to use. Figure 2.17 illustrates the process of pre-aggregation.

Figure 2.17 – Pre-aggregation for high performance

The problem with manually creating summary tables is that you have to tell the end user to go to the new table. There was no Oracle mechanism to automatically rewrite the SQL to go to the pre-created summary. Materialized views provide an alternate approach. Materialized views are very popular in Oracle systems where performance is critical and complex SQL queries exist against large tables. Generally, we see materialized views used in two areas: aggregation and replication.

In terms of aggregation, materialized views improve query speed by rewriting a query against the base table with a query against the pre-aggregated summary table via the following:

• Pre-calculated summaries—The rollup, cube, sum, avg, min, max, count(*), count(distinct x) functions can now be used to pre-summarize data.
   

• Pre-joined tables—Tables can be pre-joined to substantially improve performance.

It is important to note that a Materialized View is a form of replication.  From the moment that the Materialized View is created, it can become “stale” if any of the base-tables data is changed.  Hence, Oracle has incorporated their snapshot concept with Materialized View technology, such that all forms of replication are considered Materialized Views.

Below we see that the Oracle create snapshot syntax.  Note that we get a reply from Oracle stating “Materialized View Created.”

create snapshot
  cust_snap
on
  customer
refresh fast
  start with sysdate
  next sysdate + 1/1440
as
select * from customer@remote;