Oracle中的物化视图

原文

dba_mviews 中有大量mv的描述字段，必看。

• A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site.  A materialized view can be either read-only, updatable, or writeable.

1, read-only

Read-only materialized views do not need to belong to a materialized view group.

2, updatable

For changes made to an updatable materialized view to be pushed back to the master during refresh, the updatable materialized view must belong to a materialized view group.

3, writeable

A writeable materialized view is one that is created using the FOR UPDATE clause but is not part of a materialized view group. Users can perform DML operations on a writeable materialized view, but if you refresh the materialized view, then these changes are not pushed back to the master and the changes are lost in the materialized view itself. Writeable materialized views are typically allowed wherever fast-refreshable read-only materialized views are allowed.

Writeable materialized views are rarely used.

 

• materialized views are updated from one or more masters through individual batch updates, known as a refreshes. Oracle can refresh a materialized view using either a fast, complete, or force refresh.

1, Complete refresh

To perform a complete refresh of a materialized view, the server that manages the materialized view executes the materialized view's defining query, which essentially re-creates the materialized view. To refresh the materialized view, the result set of the query replaces the existing materialized view data.

2, Fast refresh

Fast refresh uses materialized view logs to update only the rows that have changed since the last refresh.  You can perform fast refreshes of materialized views only when the master table or master materialized view has a materialized view log. Also, for fast refreshes to be faster than complete refreshes, each join column in the CREATE MATERIALIZED VIEW statement must have an index on it.

3, Force Refresh

To perform a force refresh of a materialized view, the server that manages the materialized view attempts to perform a fast refresh. If a fast refresh is not possible, then Oracle performs a complete refresh. Use the force setting when you want a materialized view to refresh if a fast refresh is not possible.

• 我们也可以手工refresh mv：

dbms_refresh.refresh -- enables you to create groups of materialized views that can be refreshed together to a transactionally consistent point in time.
dbms_snapshot.refresh -- enables you to refresh snapshots that are not part of the same refresh group and purge logs.
dbms_mview.refresh -- oracle dbms_mview is a synonym for dbms_snapshot. oracle dbms_mview will allow you to better understand the capabilities of oracle materialized views.

BEGIN 
    dbms_snapshot.refresh( 'HR.mv_issue_category','c');--refresh mv_issue_category,方式是complete
END;

• Refresh time

1, on demand - Oracle Database refreshes this materialized view whenever an appropriate refresh procedure (例如dbms_snapshot.refresh) is called

2, on commit - Oracle Database refreshes this materialized view when a transaction on one of the materialized view's masters commits

3, never - Oracle Database never refreshes this materialized view

4, time interval - 如果创建mv时使用了NEXT语句，则指定了recurring refresh time

但通常情况，不使用next来refresh mv，而是定义一个定时job并调用dbms_snapshot.refresh等语句来refresh mv。这样的好处是通过查看job的执行情况，更好地监控mv refresh的情况。

 

• materialized view groups and refresh groups.    link