Oracle materialized View Grooming

declare--materialized views can be divided into three types:

* Contains materialized views of aggregation

* Contains materialized views of the connection only

* Nested materialized views

The limitations of the fast refresh of three materialized views are very different, but not the other way.

--Materialized View creation method (build Methods), including build immediate and build deferred two kinds.

* Build immediate generates data when the materialized view is created.

* Build deferred does not generate data at the time of creation and generates data as needed later.

The default is build IMMEDIATE.

--Materialized view query rewrite (Rewrite), including enable query Rewrite and disable query Rewrite two kinds.

Indicates whether the materialized view that was created supports query rewriting, respectively.

Query rewriting means that when querying a materialized view's base table, Oracle automatically determines whether it can be queried for materialized

Graph to get the result, if possible, avoids aggregation or join operations, and directly from the materialized view that has been computed

To read the data. The default is disable QUERY REWRITE.

When you create a materialized view, you can specify an order BY statement so that the resulting data is saved in a certain order. No

This statement is not written to the definition of the materialized view and is not valid for subsequent refreshes.

--Refresh mode for materialized view data:

Refresh only when the materialized view "needs" is refreshed (refresh), i.e., the materialized view is updated,

To ensure consistency with the base table data;

The base table has a commit, that is, the transaction commits, immediately refreshes, updating the materialized view immediately, so that the data

Consistent with the base table

The difference between the two is that the Refresh method is different.

-There are four ways to refresh: FAST, complete, force, and never.

Fast refresh takes an incremental refresh and refreshes only the changes made since the last refresh.

Complete refreshes the entire materialized view with a full refresh.

If you choose force Mode, Oracle will determine whether it can be refreshed quickly, or if it can be fast, or else complete.

Never refers to materialized views without any refreshes.

--Materialized View Log

If you need a ' quick refresh ', you need to create a materialized view log.

Materialized view logs can be built as ROWID or primary key types, depending on the need for a fast refresh of different materialized views.

You can also choose whether to include sequence, including NEW values, and a list of specified columns.

You can indicate that the on Prebuild table statement establishes a materialized view on an existing sheet. In this case, the materialized

The View and table must have the same name. When you delete a materialized view, a table with the same name is not deleted. The query rewrite for this materialized view requires the parameter

The number query_rewrite_integerity must be set to trusted or stale_tolerated.

--Materialized View partitioning

Partition-based materialized views can support partition change tracking (PCT). Materialized views with this feature, when the base table is divided

You can still perform a quick refresh operation after the zone maintenance operation. For a clustered materialized view, you can use it in the Group by list

Cube or rollup to create aggregated materialized views of different levels.

--Manual refresh of materialized views:

Refreshes the specified materialized view

Execute Dbms_mview.refresh (' mv_test ');

Refresh takes advantage of all materialized views of the table

Execute dbms_mview.refresh_defresh_dependent (' TEST ');

Refreshes all materialized views that have not been refreshed since the last refresh in the pattern

Execute dbms_mview.refresh_all_mviews;

--Characteristics of materialized views:

* Materialized view is, in a sense, a physical table (and not just a physical table);

* Materialized view is also a kind of segment (segment), so it has its own physical storage properties;

* Materialized view consumes database disk space;

--Set the parameters of the Init.ora:

Using materialized Views:

Job_queue_processes, must be set greater than 1.

Query_rewrite_enabled, when set to True, allows the query to be rewritten dynamically.

Query_rewrite_integrity, determines how much data consistency should be observed when accessing materialized views.

Optimizer_mode, must be set into a CBO in some way.

To disable materialized views:

Modify the query_rewrite_enabled parameter of the Init.ora parameter to Flase and restart the instance.

Use alter system set query_rewrite_enabled = flase, dynamic modification.

Use alter session set query_rewrite_enabled = Flash; Modify the session within.

Use the Norewrite hint.

--------------------------------------------------------------------------------------

SOURCE host: Y, target host: M

1. SOURCE table

CREATE TABLE Y.test (ID varchar2 (TEN) primary key, name VARCHAR2 (20));

2. Materialized View Log

Create:

Create materialized view Log on Y.test [tablespace mv_data with ROWID, sequence (Seq_tid)];

Delete:

Drop materialized view log on test;

2. Materialized view

Create:

CREATE materialized VIEW m.mv_test [tablespace mview_data]

BUILD IMMEDIATE

REFRESH Force

On DEMAND--refresh mode, default value, can not write

START with Sysdate

NEXT sysdate+2/1444

With PRIMARY KEY

DISABLE Query REWRITE--querying overrides, default values, can not be written

As

SELECT * from Y.test;

Delete:

Drop materialized view m.mv_test;

(Edit: Lelinpeng Source: Network)

Oracle materialized View Grooming