What is an Oracle materialized view

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.

Materialized views 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.

Materialized views can be indexed because they are physically real.

1, materialized view can be divided into the following three kinds of types

(1) A materialized view containing aggregation;

(2) contains only the materialized view of the connection;

(3) 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:

(1) Method of Creation (Buildmethods): Includes build immediate and builds deferred two.

Build immediate generates data when you create a materialized view.

Build deferred does not generate data at creation time, and then generates data as needed. The default is build IMMEDIATE.

(2) query rewriting (queryrewrite): 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 Disablequery REWRITE.

(3) Refresh: means when a DML operation occurs on the base table, when the materialized view is synchronized with 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.

2, materialized view, according to the different emphasis can have different categories:

1) According to the mode of refresh: Fast/complete/force

2) by refresh time: on Demand/on COMMIT

3 According to whether can be updated: Updatable/read only

4) whether to support query rewrite: Enable query Rewrite/disablequery REWRITE

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

Note: The materialized view that sets the refresh on commit does not have access to the remote object.

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.

3, materialized view has three kinds of refreshing way: COMPLETE, Fast and FORCE.

1) Full Refresh (COMPLETE) deletes all records in the table (if it is a single table refresh, it may take a truncate), and then regenerates the materialized view based on the definition of the query statement in the materialized view.

2 Quick Refresh (FAST) An incremental refresh mechanism that refreshes all operations on the base table from the last refresh into the materialized view. Fast must create a view log based on the primary table.

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

3 The use of force, Oracle will automatically determine whether to meet the conditions of rapid refresh, if satisfied with a quick refresh, otherwise a full refresh.

The rapid refresh mechanism of the Oracle materialized view is accomplished through materialized view logs. Oracle can also support a quick refresh of multiple materialized views through 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.

4. Other descriptions and examples of materialized view refresh clause

The REFRESH clause can contain the following sections:

[Refresh [Fast|complete|force]

[On Demand | commit]

[Start with date] [Next Date]

[With {primary key|rowid}]]