Materialized View Logs in Oracle8i and Oracle 9i
Oracle Tips by Burleson Consulting
In order to facilitate fast refreshes of
materialized views, you must create a materialized view log. If a
materialized view log is not available for a materialized view, a fast
refresh cannot be done and a complete refresh is the only refresh
mechanism. A complete refresh involves truncating the materialized
view table and then repopulating the materialized view by reexecuting
its build query. A fast refresh uses the information in the
materialized view log to only apply changes to the materialized view.
Creation of a Materialized View Log
The actual syntax of the CREATE MATERIALIZED
VIEW LOG command is rather lengthy so though I show an example here of
the creation of a materialized view log, I refer you to the SQL manual
at the documentation link (technet.oracle.com) for more details.
CREATE MATERIALIZED VIEW LOG ON
STORAGE (INITIAL 250K NEXT 250K PCTINCREASE 0)
WITH PRIMARY KEY, ROWID (tablespace_name)
INCLUDING NEW VALUES;
confused that it tells you that a snapshot log has been created;
remember, snapshots and materialized views are synonymous.This command
creates a materialized view log (or snapshot log, if you prefer) on
the table tab_example1 using the specified storage parameters and
tablespace. The log will use a parallel degree of 4 (normally this is
not required since the PARALLEL clause by itself will cause Oracle to
calculate the proper degree.) The log is not logged; rather, the
default is LOGGED (which means it is not recoverable) and will not be
cached. The default is CACHED, in the SGA. The log will track both
primary key and rowid information, as well as any changes to the
filter column TABLESPACE_NAME. Filter columns cannot be part of the
primary key and must exist in the master table. If no WITH clause is
specified, the primary key values are tracked by default. Materialized
view logs can be partitioned just like regular tables.
Altering a Materialized View Log
All aspects of a materialized view log are
alterable; these include the adding of filter columns, rowid, or
primary key data to that which is all ready being stored in the log.
For this task, too, I refer you to the documentation link at
technet.oracle.com for the details.
Dropping a Materialized View Log
The command for dropping a materialized view
log is simple:
MATERIALIZED VIEW LOG ON [schema.]tablename;
Administration of 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.
Code Depot for Full Scripts
||This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.