ss

Oracle Tips by Burleson

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