Oracle materialized views

In real work, there will be multiple data sources synchronized to a database to complete the data analysis of the scene, the data can not be synchronized in real-time, we generally through the scheduled task of extracting data to the statistical analysis library for the application to use.

The general synchronization method can be done through the time stamp to do the full and incremental data synchronization (there is the possibility of the original data changes, data inconsistency), can also be done by Dblink data real-time query (more loss of online database performance), the general best way is through the establishment of materialized views, and then through the schedual The job completes timing data synchronization, where the use of materialized views is recorded.

I. Introduction to materialized views

Materialized views are a special physical table, and the materialized view is relative to the normal view. The normal view is a virtual table, the limitations of the application are large, any query on the view, Oracle is actually converted into a query of the views SQL statement. This has no substantial benefit in improving overall query performance.

1. Materialized View classification

On DEMAND: The materialized view "needs" to be refreshed before refreshing (refresh), that is, to update materialized views to ensure consistency with the base table data;

On commit: once the base table has a commit, that is, the transaction commits, immediately refresh, update the materialized view immediately, so that the data and the base table is consistent ;

Create materialized view By default does not specify type, it is on-demand refresh (on demand)

2. Materialized view

Second, materialized view use

1. Materialized View creation

Materialized view data is derived from the base table, and the starting point of the refresh is recorded in the materialized view log, so creating a materialized view authorization must have a base table--materialized view log (base table based)--materialized view

Materialized View Creation Example:

Create materialized View T under Dbtest, where the base table is the Dept table under the Scott user (1) Authorized dbtest users can query scott.deptGrant Select  onScott.dept todbtest; (2) Create a table t under the dbtest user (if you create a materialized view PlusOn prebuilt table)
Create TableT as Select *  fromScott.deptwhere 1=2;
(3Create a materialized view log under the Scott user and create a materialized view under Dbtest t
To create a materialized view log:
Conn Scott/Tiger;
CreateMaterializedView Log  onDept;
Grant SELECT on Mlog$_dept to Dbtest;
To create a materialized view:
Conn Dbtest/dbtest;
CreateMaterializedViewT
On prebuilt table
Refresh Fast onDemand asSelectDeptno,dname,loc,acolumn fromscott.dept;

# #可以通过在 View T and add build immediate parameter immediately refresh materialized view, get data

The REFRESH clause can contain the following sections:
[Refresh [Fast|complete|force]
[On Demand | commit]
[Start with date] [Next Date]
[With {primary KEY|ROWID}]

2. Materialized View Refresh

When the base table has an update (DML), if it is not on the commit type, the materialized view needs to be refreshed for the data to remain consistent with the base table, refreshed with full refresh (complete), fast flush (incremental fast), forced flush (force), not refreshed (never)

Fast: Incremental Quick Refresh

Dbms_mview.refresh (' table name ''F')  

Complete: Full Volume refresh

exec Dbms_mview.refresh (' table name ''C')   ;

Force: Determine if refresh can be quickly refreshed, if it can be quickly refreshed, perform a fast refresh, if not, perform a complete refresh

Never: Not refreshed

3. Materialized View Delete

Drop VIEW  Mview_name;

4. Materialized View Log Delete

Materialized view logs are mlog$_basetablename named format

DROP VIEW LOG   on Base_table_name;

 mlog$_dept DEPTNO primary key column snaptime$$ is used to indicate the refresh time dmltype$$ is used to represent the DML operation type, I means insert,d indicates that delete,u indicates that the update old_new$$ is used to indicate whether the value is a new value or an old value. N (EW) represents the new value (typically the delete operation), the O (LD) represents the old value (typically an insert operation), and U represents the update operation. change_vector$$ represents a modified vector to indicate which or which fields are modified xid$$ if with followed by primary  key 
 When a DML operation occurs on a base table, it is logged to the materialized view log, at the specified time January 1, 4000 0:0 0 seconds (materialized views are not refreshed). 
 If the materialized view log is used by more than one materialized view, a materialized view refreshes the time it refreshes the record to its refreshed time. 
 The materialized view log can be used only if a materialized view of the fast refresh is established, and if only one materialized view is established, the materialized views log will be cleared after the materialized view is refreshed 
  

--Oracle creates Temporal table rupd$_ base table when creating materialized view logs using primary key

5. View materialized view

Set  $ ; Set 20000     for for A60; Select  from Dba_mviews;

#如果要看具体语句, you can view it through the query field

Third, appendix

Materialized views are a special physical table, and the materialized view is relative to the normal view. The normal view is a virtual table, the limitations of the application are large, any query on the view, Oracle is actually converted into a query of the views SQL statement. This has no substantial benefit in improving overall query performance.

Oracle materialized views