Administration of 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 tab_example1
STORAGE (INITIAL 250K NEXT 250K PCTINCREASE 0)
TABLESPACE view_logs
PARALLEL 4
NOLOGGING
NOCACHE
WITH PRIMARY KEY, ROWID (tablespace_name)
INCLUDING NEW VALUES;

       Don’t be 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:

DROP 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.

See 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.