Oracle materialized View Syntax ____oracle

Create materialized view [view_name]
Refresh [Fast|complete|force]
[
On [Commit|demand] |
Start with (start_time) next (next_time)
]
As
{Create a query statement for materialized views}

These are the common syntax for Oracle to create materialized views (materialized view, hereinafter referred to as MV), with the following meanings:

1.refresh [Fast|complete|force] View Refresh mode:
Fast: Incremental Refresh. Assuming the previous refresh time is T1, when refreshing the materialized view using the fast mode, only add T1 to the view to the current time period, the master table has changed. To record this change, an materialized view Log table is also required to establish an incremental refresh materialized view. Create materialized view Log on (the primary table name).
Complete: Refresh all. A query statement that is equivalent to performing a new view creation.
Force: This is the default data refresh method. When you can use fast mode, the data refresh will be in fast mode; otherwise, use the complete method.

2.MV Data Refresh Time:
On demand: Refresh when the user needs to refresh, and this requires the user to manually refresh the data (or you can use job refresh)
On commit: When the data in the main table is submitted, immediately refresh the data in the MV;
Start ... : Start at a specified time and refresh once at intervals (as specified by next);

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 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 refers to the materialized view when users need to refresh, you can manually through the Dbms_mview. Refresh, and so on, can also be refreshed through the job timing. On commit indicates that the materialized view is being refreshed while the DML operation of the base table is committed. There are four ways to refresh: FAST, COMPLETE, Force, and neve*. The **st refresh takes an incremental refresh and refreshes only the modifications made since the last refresh. The complete refresh completely refreshes the entire materialized view. If you choose force, Oracle will be refreshed to determine whether a quick refresh can be done, if you can use the fast method, otherwise use the complete method. Never refers to materialized views without 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.

The basic operation and use of materialized views can be viewed in the URL: http://blog.itpub.net/post/468/13318 related Dongdong. I mainly explain the use of materialized views of the basic Dongdong. such as how to build on a specific table space, these in other materialized views have almost nothing to introduce. I do it mainly as an example,
If the basic concepts of materialized views are clear, there is a better understanding of where to write specific tablespace storage.

You should create a stored log space before you create a materialized view

CREATE materialized VIEW LOG on Mv_lvy_levytaxbgtdiv
Tablespace zgmv_data-Log is saved in a specific table space
With ROWID;
CREATE materialized VIEW LOG on Tb_lvy_levydetaildata
Tablespace zgmv_data-Log is saved in a specific table space
With Rowid,sequence (Levydetaildataid);
CREATE materialized VIEW LOG on Tb_lvy_levydata
Tablespace zgmv_data-Log is saved in a specific table space
With Rowid,sequence (Levydataid);
Then create a materialized view
--Creating materialized views
Create materialized View Mv_lvy_levydetaildata
Tablespace Zgmv_data--Save the table space
Build DEFERRED--Lazy refresh does not refresh immediately
Refresh Force--Quick Refresh if you can quickly refresh, otherwise full refresh
On demand--refresh in the specified manner
Start with To_date (' 24-11-2005 18:00:10 ', ' dd-mm-yyyy hh24:mi:ss ')--First Refresh Time
Next TRUNC (sysdate+1) +18/24--Refresh time interval
As
SELECT Levydetaildataid, Detaildatano, Taxtermbegin, Taxtermend,
......
ROUND (Taxdeduct * taxpercent1, 2)-ROUND (Taxdeduct * taxpercent2, 2)-
ROUND (Taxdeduct * taxpercent3, 2)-ROUND (Taxdeduct * taxpercent4, 2)-
ROUND (Taxdeduct * taxpercent5, 2) taxdeduct, ROUND (taxfinal * taxpercent1, 2)-
ROUND (Taxfinal * taxpercent2, 2)-ROUND (Taxfinal * taxpercent3, 2)-
ROUND (Taxfinal * taxpercent4, 2)-ROUND (Taxfinal * taxpercent5, 2) taxfinal,
A.levydataid, A.budgetitemcode, Taxtypecode,
......
From Tb_lvy_levydetaildata A, tb_lvy_levydata C, Mv_lvy_levytaxbgtdiv b
WHERE A.levydataid = C.levydataid
and A.budgetdistrscalecode = B.budgetdistrscalecode
and A.budgetitemcode = B.budgetitemcode
and C.incomeresidecode = B.rcvfisccode
and c.taxstatuscode= ' 08 '
and c.negativeflag!= ' 9 '
Delete materialized view Log
--Delete materialized views:
--Delete log: Drop materialized view Log on Mv_lvy_levytaxbgtdiv;
DROP materialized view Log on Tb_lvy_levydetaildata;
DROP materialized view Log on Tb_lvy_levydata;
--Delete Materialized view drop materialized view mv_lvy_levydetaildata;
--the basic and the operation of the table is consistent-materialized view because it is physically real, so you can create an index.
The method is created in the same way as normal table creation, and is not written repeatedly.