Go: materialized views in Oracle

Materialized View creation Syntax:
CREATE materialized VIEW <schema.name>
PCTFREE <integer>--Storage Parameters
pctused <integer>--Storage Parameters
Tablespace <tablespace_name>--Table Space
BUILD immediate| deferred--How to create
enable| DISABLE query rewrite--support for querying overrides
REFRESH <force| Fast| complete| Never> on <commit | demand> [With<primary key| rowid| Sequence>][including NEW VALUES] [<start with date> <next date>]--data refresh mechanism, if you need a quick refresh, you need to establish a materialized view log, Add a with clause, and you need to create a materialized view log
[On prebuilt table]--creates a materialized view on a table that already exists, and the query rewrite for this materialized view requires that the parameter query_rewrite_integerity be set to trusted or stale_tolerated
As (<sql statement>;

Create materialized view Log on TableName with [PRIMARY key| ROWID];

Drop materialized view Log on TableName;

Drop materialized view ViewName;

EXEC dbms_mview. REFRESH (' Cyts. Vm_accbill_job_fee ');

Oracle's materialized views provide powerful capabilities to pre-compute and save the results of more time-consuming operations such as table joins or aggregates, so that when you execute a query, you can avoid these time-consuming operations and get results quickly. Materialized views are similar in many respects to indexes: the purpose of using materialized views is to improve query performance; Materialized views are transparent to the application, adding and removing materialized views does not affect the correctness and validity of the SQL statements in the application; Materialized views need to occupy storage space, and materialized views should be refreshed when the base table changes.

Materialized views can be divided into the following three types: a materialized view containing aggregates, a materialized view that contains only connections, and a nested materialized view. The limitations of the fast refresh of three materialized views are very different, but not the other way. You can specify several options when you create a materialized view, following a brief description of several major choices:

Creation mode (Build Methods): Includes two types of build immediate and build deferred. Build immediate generates data when a materialized view is created, and build deferred does not generate data when it is created, and generates data as needed later. The default is build IMMEDIATE.

Query Rewrite: Includes the 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 the query materialized views can be used to get results, and if so, avoids aggregation or join operations and reads data directly from the materialized views that have been computed. The default is disable QUERY REWRITE.

Refresh: Refers to when the materialized view is synchronized with the base table when a DML operation occurs on the base table. There are two modes of refresh: On demand and on COMMIT. On demand refers to materialized views that are refreshed when required by the user and can be manually dbms_mview. Refresh, or refresh by job timing. On commit indicates that the materialized view is refreshed at the same time that the DML operation of the base table is committed. There are four ways to refresh: FAST, complete, force, and never. The RST refresh takes an incremental refresh and refreshes only the modifications 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. The default value is force on DEMAND.

When you create a materialized view, you can specify an order BY statement so that the resulting data is saved in a certain order. However, this statement is not written into the definition of materialized views and is not valid for subsequent refreshes.

Materialized view logs: If a quick refresh is required, the materialized view log needs to be established. 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 view and the table must have the same name. When you delete a materialized view, a table with the same name is not deleted. Query rewriting for this materialized view requires that the parameter query_rewrite_integerity must be set to trusted or stale_tolerated.

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

The basic operation and use of materialized views can be viewed in the URL: http://blog.itpub.net/post/468/13318 related stuff. I mainly explain the use of materialized views of the basic stuff. such as how to build on a particular table space, these in other materialized views are almost none of the introduction. It's mostly done with an example of me,
If the basic concepts of materialized views are clear, it is more obvious that there is a specific table space stored there.

Creating a materialized view should create a stored log space first

CREATE materialized VIEW LOG on Mv_lvy_levytaxbgtdiv
Tablespace Zgmv_data--logs are saved in a specific table space
With ROWID;
CREATE materialized VIEW LOG on Tb_lvy_levydetaildata
Tablespace Zgmv_data--logs are saved in a specific table space
With Rowid,sequence (Levydetaildataid);
CREATE materialized VIEW LOG on Tb_lvy_levydata
Tablespace Zgmv_data--logs are saved in a specific table space
With Rowid,sequence (Levydataid);
Then create the materialized view
--Create materialized views
Create materialized View Mv_lvy_levydetaildata
Tablespace zgmv_data--Save table space
BUILD DEFERRED--Deferred refresh does not refresh immediately
Refresh Force--refreshes quickly 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 time refresh
Next TRUNC (sysdate+1) +18/24--Refresh 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 logs
--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;
--basic and consistent with the operation of the table--materialized views can create indexes because they are physically real.
Created in the same way as normal tables, it is not duplicated.

Go: materialized views in Oracle