Oracle Materialized View

A materialized view is a database object that contains a query result. It is a local copy of remote data or is used to generate a summary table based on the sum of data tables. Materialized View stores data based on remote tables, also known as snapshots.

Materialized views can be used to query tables, views, and Other materialized views.

A materialized view is usually called a master table (during replication) or a schedule (in a data warehouse ).

For replication, materialized views allow you to locally maintain copies of remote data, which are read-only. To modify a local copy, you must use the advanced copy function. When you want to extract data from a table or view, you can extract data from the materialized view.

For a data warehouse, the created materialized view is usually an aggregation view, a single table aggregation view, and a connection view.

In this article, we will see how to create a materialized view and discuss its refresh options.

In the replication environment, the created materialized view usually contains the primary key, rowid, and subquery view.

1. Primary Key Materialized View:

The following syntax creates a primary key Materialized View on the remote database table EMP.

SQL> Create materialized view mv_emp_pk

Refresh fast start with sysdate

Next sysdate more than 1/48

With primary key

As select * from EMP @ remote_db;

Materialized View created.

Note: When you use the fast option to create a materialized view, you must create a view log based on the master table, as shown below:

SQL> Create materialized view log on EMP;

Materialized view log created.

2. rowid Materialized View

The following syntax creates a rowid Materialized View on the remote database table EMP.

SQL> Create materialized view mv_emp_rowid

Refresh with rowid

As select * from EMP @ remote_db;

Materialized view log created.

3. Sub-query Materialized View

The following syntax creates a materialized view for sub-queries based on the EMP and dept tables on the remote database table EMP.

SQL> Create materialized view mv_empdept

As select * from EMP @ remote_db E

Where exists

(Select * from Dept @ remote_db d

Where E. dept_no = D. dept_no)

Materialized view log created.

Refresh clause

[Refresh [Fast | complete | force]

[On Demand | commit]

[Start with date] [next date]

[With {primary key | rowid}]

Refresh option description:

A. Oracle uses the refresh method to refresh data in the materialized view.

B. Is the materialized view based on the primary key or rowid?

C. Refresh time and interval of materialized views

Refresh method-fast clause

Incremental refresh uses the materialized view log (as described above) to send the modified data rows of the master table to the materialized view. If the refresh fast clause is specified, create a materialized view log for the master table.

SQL> Create materialized view log on EMP;

Materialized view log created.

For the incremental refresh option, if an analysis function exists in the subquery, the materialized view does not work.

Refresh method-complete clause

Completely refresh to regenerate the entire view. If the request is completely refreshed, Oracle will completely refresh even if the incremental refresh is available.

Refresh method-force clause

When the force clause is specified, if incremental refresh is available to Oracle, incremental refresh will be completed; otherwise, full refresh will be completed. If the refresh method (fast, complete, or force) is not specified, the force option is the default option.

Primary Key and rowd clause

The with primary key option generates a primary key materialized view, that is, the materialized view is based on the primary key of the primary table, rather than the rowid (corresponding to the rowid clause ). primary Key is the default option. To generate a primary key clause, you should define a primary key in the primary table. Otherwise, you should use a rowid-based materialized view.

The materialized view of the primary key allows you to identify the primary table of the materialized view without affecting the availability of incremental refresh of the materialized view.

The rowid materialized view has only one primary table and cannot contain any of the following items:

N distinct or aggregate function.

N group by, subquery, connection, and set operations

Refresh time

The start with clause instructs the database to complete the first copy time from the master table to the local table. The next clause describes the interval of refreshing.

SQL> Create materialized view mv_emp_pk

Refresh fast

Start with sysdate

Next sysdate + 2

With primary key

As select * from EMP @ remote_db;

Materialized View created.

In the preceding example, the first copy of the Materialized View data is generated at creation and refreshed every two days.

Http://blog.china.com/u/071018/85669/200710/1135782.html