Oracle materialized View detailed

I. Theoretical part

The materialized view of Oracle 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.

The materialized views of Oracle provide powerful features that can be used to anticipate and save the results of time-consuming operations such as table joins or clustering, so that when you execute a query, you can avoid these time-consuming operations and get results quickly. There are many aspects of materialized views that are similar to indexes: The purpose of using materialized views is to improve query performance; Materialized views are transparent to applications, and adding and removing materialized views does not affect the correctness and validity of SQL statements in an application; Materialized views require storage space; Materialized views should also be refreshed when the base table is changed.

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

REFRESH clause

[Refresh [Fast|complete|force]

[On Demand | commit]

[Start with date] [Next Date]

[With {primary key|rowid}]]

By default, if the Refresh method and refresh mode are not specified, Oracle defaults to force and demand.

Materialized views can be grouped into the following three types: materialized views that contain aggregates, materialized views that contain only connections, and nested materialized views. The restrictions on the rapid refresh of three materialized views vary greatly, but not in others. When you create a materialized view, you can specify a variety of options, following a brief description of several key choices:

Create Methods: Includes build immediate and builds deferred two species. Build immediate generates data when you create materialized views, and build deferred does not generate data at creation time, and then generates data as needed. The default is build IMMEDIATE.

Query rewrite Rewrite: Includes the Enable query Rewrite and disable query Rewrite two. Indicates whether the materialized view you created supports query rewriting. Query rewriting means that when querying the base table of materialized views, Oracle automatically determines whether the results can be obtained by querying materialized views and, if so, avoids aggregation or join operations and reads data directly from the materialized view that has already been computed. The default is disable QUERY REWRITE.

Refresh: Refers to how materialized views are synchronized with the base table when a DML operation occurs on the base table. There are two modes of refreshing: On demand and on COMMIT.

On demand and on commit materialized views differ in their refresh methods, on demand refers to the materialized view when users need to refresh, you can manually through the Dbms_mview. Refresh, and so on, it can also be refreshed through job timings, that is, updating materialized views to ensure consistency with base table data; On commit is that once the base table has a commit, a transaction commits, it refreshes immediately and updates the materialized view immediately, making the data consistent with the base table.

For a base table, the usual commit can be completed in 0.01 seconds, but it takes 6 seconds after the on Commit view. The speed has been reduced many times. The effect of the on Commit view on the base table is evident.

There are four ways to refresh: FAST, COMPLETE, Force, and never.

1. FAST: Incremental refresh use materialized view logs to send data rows that have been modified by the primary table to materialized views. If you specify the Refresh FAST clause, you should create a materialized view log on the primary table, so when you create a materialized view with the FAST option, you must create a view log based 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.

2. COMPLETE: Refreshes the entire materialized view for a full refresh.

3. FORCE: When the FORCE clause is specified, a full refresh is complete if the incremental flush is available to Oracle, or the FORCE option is the default option if you do not specify a Refresh method (FAST, COMPLETE, or FORCE).

4. NEVER: means the materialized view does not make any refreshes. The default value is force on DEMAND.

When you create a materialized view, you can specify an order by statement to keep the resulting data in a certain sequence. However, this statement is not written to the definition of the materialized view and is not valid for subsequent refreshes.

Materialized View log: If you need a quick refresh, you need to establish a materialized view log. Materialized view logs can be established as ROWID or primary key types, depending on the need for rapid refresh of different materialized views. You can also choose whether to include sequence, including NEW values, and a list of the specified columns.

You can indicate on the Prebuild table statement that the materialized view is built on a table that already exists. In this case, the materialized view and the table must have the same name. When you delete a materialized view, the table with the same name is not deleted. The query rewrite of this materialized view requires that the parameter query_rewrite_integerity be set to trusted or stale_tolerated.

Materialized views can be partitioned. and partitioned materialized views can support zoning change tracking (PCT). Materialized views with this feature can still be quickly refreshed when the base table is partitioned for maintenance operations. For a clustered materialized view, you can use cube or rollup in the group by list to establish different levels of aggregated materialized views.

PRIMARY KEY and Rowd clause: 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, not 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:

1. Distinct or aggregate functions.

2. Group by, subquery, connection and set operations

Two. Materialized View instances

The rapid refresh of materialized views employs an incremental mechanism that refreshes only the data that changes on the base table. Therefore, quick Refresh is the first choice for materialized view refresh mode.

However, quick refreshes are more restrictive and more so for materialized views that are quickly refreshed with on commit mode. The quick refresh mechanism for materialized views that contain aggregates and include joins is not the same, and there is an additional requirement for quick refreshes of multi-tier nested materialized views. So many limitations are generally hard to remember, and when a materialized view fails, Oracle's error message is too simplistic to be able to pinpoint the cause of the problem.

Oracle-provided Dbms_mview. The Explain_mview process can help you quickly locate the cause of the problem. The following is an example of how to solve a problem through this process.

To create a fast-refreshed nested materialized view:

Sql> CREATE TABLE B (ID number PRIMARY KEY, NAME VARCHAR2 (30));

Table has been created.

Sql> CREATE TABLE C (ID number PRIMARY KEY, NAME VARCHAR2 (30));

Table has been created.

Sql> CREATE TABLE A (ID number, BID number, CID number, NUM number,

2 CONSTRAINT fk_a_b_bid FOREIGN KEY (BID) REFERENCES B (ID),

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45465.htm