Two typical application scenarios for Oracle materialized views

Materialized view is a more characteristic of Oracle, since Oracle9i, the application is very extensive, unlike MySQL, does not support the native materialized view, with the help of flexviews to achieve. What is the use of materialized views? To answer this question, you must first understand the difference between a materialized view and a normal view:

Materialized views have a table of containers corresponding to them. The container table is a "rule" table with the same name as the materialized view, which stores the result set returned by the query. This is the fundamental difference between materialized view and normal view, it is the "physical existence" of the stored result set, and the normal view does not have this physical existence, just a virtual table, every visit, the query will perform a base table access (regardless of cache).

There are two application scenarios for materialized views: 1, for query Optimization 2, for advanced replication, to illustrate some of the actual work of the case.

1, query optimization.

A telecom value-added service, when the process starts, it is necessary to load some important business initialization data (such as the networkid of global operators, CC, NDC and other basic data), these business initialization data to be obtained from four tables query. To improve access performance for this part of the data and speed up the process startup, you can combine the data from these four tables into a materialized view, defined as follows:

create materialized VIEW irdb_networklist
REFRESH Force on COMMIT
as
SELECT
   a.networkid as Networkid,
   a.networkname as NAME,
& nbsp;  c.cc as CC,
   C.ndc as NDC,
   b.mcc as MCC,
   B.MNC as MNC,
  ;  A.newvisitinterval as Newvisitinterval,
   a.outnewvisitinterval as Outnewvisitinterval,
   d.mnp_enabled as HASMNP,
   a.brandname as brandname,
   A.languagecode as Defaultlanguagecode,
   c.timezone as TIMEZONE,
   a.ndd as NDD,
   A.zoneid as ZoneID
from
Irdb_network_master A, Irdb_network_gsm_detail B, Irdb_network_codes C, Irdb_country_master D
WHERE
A.networkid = b.networkid and A.networkid = c.networkid and A.countryid = D.countryid and a.status = ' 1 ' and c.st ATUs = ' 1 '

Follow us on irdb_network_master,irdb_network_gsm_detail,irdb_network_codes and irdb_ Country_master The materialized view irdb_networklist can be updated automatically when any one or more of the four business base tables are in the DML commit. Of course, the synchronization of the base table and materialized view has a certain price, but if the materialized view is not established, then each external call will query the base table, and the materialized view will scatter the pressure, stagger the base table query, the base table connection and the external service interface, and help reduce the peak value of the database load. This is also one of the core ideas of database performance optimization.

2. Advanced Replication

Many business scenarios, we do not need to synchronize the entire database, only some parts of the table to synchronize some of the fields, this time, materialized view can come in handy. The following is a specific municipality mobile ringtone business database structure diagram:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6E/B0/wKioL1WC9wWi9kjfAAF0aiD-Lls713.jpg "title=" P650.jpg "alt=" Wkiol1wc9wwi9kjfaaf0aid-lls713.jpg "/>

Here, a P650 small machine as a management node, responsible for business spending, business, such as data processing, the remaining 5 P650 as a call node use, only provide user data query function. There is no need to use a full-Library synchronization technology like DG, because the call node only needs to synchronize some of the data related to the call business. Management node to create materialized views, here is an example of the T_userinfo user table:

CREATE materialized VIEW

Usdptemp. T_userinfo_mv

REFRESH FAST

As

Selectphonenumber,localid,brandid,paykind from usdp604. T_userinfo

Establish a synonym on the call node:

Create or replace synonym T_userinfo
For T_USERINFO_MV;

This way, you can maintain exactly the same object name as the management node.

Then create a refresh group that refreshes the materialized view every 10 seconds:

--create a refresh Group

BEGIN

Dbms_refresh. Make (

name = ' Usdpsync ',

list = ' T_USERINFO_MV ',

next_date= sysdate,

interval = ' sysdate + 10/86400 '

);

END;

/

The T_USERINFO_MV on the call node is IMPDP from the temporary user on the management node, with the Dblink, so the materialized view logs on the T_USERINFO_MV on the call node and on the management node are tied together.

Materialized views in the use of the process, there are several points to note, here are listed:

1, materialized view has two refresh mode on demand and on COMMIT, the former need to call Dbms_mview manual refresh, the latter will be automatically refreshed when the base table commits. If a materialized view is created without specifying a refresh mode, the default is on DEMAND, where a job is normally required to call Dbms_mview in the job according to a fixed period of time.
Row refreshes.

2. Materialized view log has many options when it is established: You can specify several types of rowID, PRIMARY key, and object ID, and you can specify sequence or explicitly specify a column name. The structure of the materialized view logs generated by the above scenarios is not the same.

3, there are four ways to refresh: Fast, complete, force and never.
Fast is an incremental refresh that refreshes only the changes made after the last refresh.
Complete is a full refresh of the entire materialized view.
Force,oracle will determine if it can be refreshed quickly, if it can be fast, otherwise complete
Never, materialized views do not make any refreshes.

If you want to make a quick refresh, you must establish a materialized view log. Materialized view log naming rules are mlog$_+ base table names.
To view the materialized view Log table for the current system:
SELECT * from Dba_mview_logs;

Force mode refresh does not require the creation of materialized view logs.

4. If the materialized view encounters a synchronization problem, you can perform a manual refresh in case of an emergency:

sql> exec Dbms_mview.refresh (' irdb_networklist ');
PL/SQL procedure successfully completed

The irdb_networklist materialized view is refreshed based on the base table.

5. Materialized View Log optimization:

For materialized view logs, we can build indexes to improve performance. At the same time in troubleshooting synchronization slow performance problems also need to check to see if there is a high water level, materialized view Log table high water level will affect the refresh performance, processing high water level method, omitted here.

This article is from "Memory Fragment" blog, declined reprint!

Two typical application scenarios for Oracle materialized views