René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Materialized views in Oracle

A materialized view is a stored summary containing precomputes results (originating from an SQL select statement).
As the data is precomputed, materialized views allow for (seemingly) faster dataware query answers

Types of materialized views

There are three types of materialized views:
  • Read only materialized view
  • Updateable materialized view
  • Writeable materialized view

Read only materialized views

Advantages:
  • There is no possibility for conflicts as they cannot be updated.
  • Complex materialized views are supported

Updateable materialized views

Advantages:
  • Can be updated even when disconnected from the master site or master materialized view site.
  • Requires fewer resources than multimaster replication.
  • Are refreshed on demand. Hence the load on the network might be reduced compared to using multimaster replication because multimaster replication synchronises changes at regular intervalls.
Updateable materialized views require the advnced replication option to be installed.

Writeable materialized views

They are created with the for update clause during creation without then adding the materialized view to a materialized view group. In such a case, the materialized view is updatable, but the changes are lost when the materialized view refreshes.
Writeable materialized views require the advnced replication option to be installed.

Query rewrite

... yet to be finished ..
The query rewrite facility is totally transparent to an application which needs not be aware of the existance of the underlying materialized view.

Refreshing process

Refreshing a materialized view

Refreshing a materialized view synchronizes is with its master table.
Oracle performs the following operations when refreshing a materialized view. In the case of a complete refresh (using dbms_mview.refresh)
  1. sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh.
  2. The materialized base view is truncated.
  3. All rows selected from the master table are inserted into the snapshot base table.
  4. sys.slog$ is updated to reflect the time of the refresh.
In the case of a fast refresh, the steps are:
  1. sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh.
  2. Rows in the materialized base view are deleted.
  3. All rows selected from the master table are inserted into the snapshot base table.
  4. sys.slog$ is updated to reflect the time of the refresh.
  5. Rows that are not needed anymore for a refresh by any materialized view are deleted from the materialized view log (<schema name>.MLOG$_table)
If a materialized view is being refreshed can be checked by querying the type of v$lock: if the type is JI a refresh is being performed.
The following query checks for this: 
select 
  o.owner        "Owner",
  o.object_name  "Mat View",
  username       "Username",
  s.sid          "Sid"
from
  v$lock         l,
  dba_objects    o,
  v$session      s
where
  o.object_id   = l.id1    and
  l.type        ='JI'      and
  l.lmode       = 6        and
  s.sid         = l.sid    and
  o.object_type = 'TABLE'

Errors during the automatic refresh of materialized views

If an error occurs during the automatic refresh of a materialized view, an error message is written into the alert.log.

Thanks

Thanks to Wm. Scott Lewis, Bhavani Shankar and Jim Kotan who each pointed out an error on this page.