Oracle materialized View Introduction ____oracle

Materialized views in Oracle

A materialized view is a database pair that includes a query result, which is a local copy of the remote data, or is used to generate a summary table based on the sum of the data tables. Materialized views store data that is based on remote tables, or it can be called snapshots.

Materialized views can query tables, views, and other materialized views.

Typically, a materialized view is called a primary table (during replication) or a schedule (in a data warehouse).

For replication, materialized views allow you to maintain a copy of remote data locally, which is read-only. If you want to modify the local copy, you must use the Advanced Copy feature. When you want to extract data from a table or view, you can extract it from the materialized view.

For a data warehouse, the materialized view that is created is typically 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 a replication environment, the materialized views created typically have primary keys, ROWID, and subquery views.

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

NEXT Sysdate + 1/48

As SELECT * from emp@remote_db;

Materialized view created.

Note: When you create a materialized view with the fast option, you must create a view log based on the primary table, as follows:

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

Materialized view log created.

3. Sub-query materialized view

The following syntax creates a materialized view of subqueries 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 refreshes data in materialized views with the Refresh method.

B. Materialized views based on primary key or ROWID

C. Refresh time and interval refresh time for materialized views

Refresh Method-fast clause

Incremental refreshes send the data rows that the primary table has modified to the materialized view using the materialized view log (as described above). If you specify the refresh fast clause, you should create a materialized view log on the primary table

Sql> CREATE materialized VIEW LOG on EMP;

Materialized view log created.

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

Refresh Method-complete clause

Full refresh rebuilds the entire view, and if the request is fully refreshed, Oracle completes the full refresh even if the incremental refresh is available.

Refresh method–force clause

When the FORCE clause is specified, a full refresh is complete if an incremental flush is available to Oracle, or if the Refresh method (FAST, COMPLETE, or FORCE) is not specified, the FORCE option is the default option

primary keys and ROWD clauses

The WITH PRIMARY key option generates a primary key materialized view, which means that 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, in order to generate the PRIMARY key clause, you should define a primary key on the primary table, or you should use a materialized view based on rowID.

The primary key materialized view allows you to recognize materialized view master tables without affecting the availability of materialized view incremental refreshes.

rowID materialized views have a single primary table and cannot include any of the following:

n Distinct or aggregate functions.

n Group by, subqueries, joins, and set operations

Refresh Time

The START with clause notifies the database of the time it was first replicated from the primary table to the surface, and should estimate the time of the next run, and the next clause describes the interval between refreshes.

Sql> CREATE materialized VIEW MV_EMP_PK

NEXT Sysdate + 2

As SELECT * from emp@remote_db;

Materialized view created.

In the example above, the first copy of the materialized view data is generated when it is created and refreshed once every two days.

Summary

Materialized views provide a scalable view based on primary key or ROWID, specifying the Refresh method and time of automatic refresh

Reproduced from: http://blog.csdn.net/superskk6/article/details/7410230