||Oracle Tips by Burleson
Chapter 2 Physical Entity Design for
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
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
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
start with sysdate
next sysdate + 1/1440
select * from customer@remote;
||Need an Oracle Health Check?
Does your boss blame you for an Oracle performance problem?
Need to prove that your database is properly optimized?
BC Oracle performance guru's can quickly verify every aspect of your
Oracle database and provide a complete certification that your database
is fully optimized.