Oracle Tips by Burleson
Materialized Views (Snapshots) in Oracle8i and Oracle9i
Another feature of Oracle that needs
administration is the snapshot (also known as a materialized view.)
Snapshots are copies of an entire single table or set of its rows
(simple snapshot), or a collection of tables, views or their rows
using joins, grouping, and selection criteria (complex snapshots).
Snapshots are very useful in a distributed environment where remote
locations need a query-able copy of a table from the master
database. Instead of paying the penalty for using the network to
send out the query and get back the data, the query is against a
local table image and is thus much faster. With later versions of
Oracle7 and in Oracle8, Oracle8i, and Oracle9i, snapshots can be
made updatable. As stated in Section 7.5, the new materialized view
is actually a special form of “same database” snapshot.
Snapshots and materialized views are
asynchronous in nature; that is, they reflect a table’s or a
collection’s state at the time the snapshot was taken. A simple
snapshot or materialized view can be periodically refreshed either
by use of a snapshot log, containing only the changed rows for the
snapshot (fast refresh), or a totally new copy (complete refresh).
In most cases, the fast refresh is quicker and just as accurate. A
fast refresh, however, can be used only if the snapshot or
materialized view has a log, and that log was created prior to the
creation or last refresh of the snapshot. For a complex snapshot or
materialized view, a complete refresh is required. It is also
possible to allow the system to decide which to use, either a fast
or complete refresh.
One problem with a snapshot or materialized
view log is that it keeps a copy of each and every change to a row.
Therefore, if a row undergoes 200 changes between one refresh and
the next, there will be 200 entries in the snapshot or materialized
view log that will be applied to the snapshot at refresh. This could
lead to the refresh of the snapshot or materialized view taking
longer than a complete refresh. Every snapshot or materialized view
should be examined for the amount of activity it is experiencing,
and if this is occurring with any of them, the snapshot or
materialized view log should be eliminated or the refresh mode
changed to COMPLETE.
A materialized view is simply a snapshot
that is contained in the current instance instead of a remote
instance. Other than the keyword MATERIALIZED VIEW, the CREATE
SNAPSHOT and CREATE SNAPSHOT LOG commands are identical to the
CREATE MATERIALIZED VIEW and CREATE MATERIALIZED VIEW LOG commands.
Since the CREATE MATERIALIZED VIEW command creates a view, table,
and an index, to maintain the materialized view, you must have the
CREATE VIEW, CREATE TABLE, CREATE INDEX, and CREATE MATERIALIZED
VIEW or CREATE SNAPSHOT privileges to create a materialized view. If
you wish query rewrite to be available on the materialized views
created, the owner of the underlying tables and the materialized
view must have the QUERY REWRITE privilege, or the creator of the
materialized view must have the GLOBAL QUERY REWRITE privilege.
In a data warehousing situation, a
materialized view can be used by Oracle to rewrite queries on the
fly that the optimizer determines would profit from using the
materialized view rather than the base tables. You should take this
into consideration when the concurrency of the data is important,
since a materialized view is only as current as its last refresh.
See Code Depot