Oracle materialized View

Transferred from: http://blog.csdn.net/zhangchu_63/article/details/5543377

The following connections are the theory of materialized views: http://blog.csdn.net/tianlesoftware/article/details/4713553

The company's project encountered the need to take data from the database of other systems to carry out statistical analysis of the problem, the initial choice of using Oracle's ODI tool to draw the number, but the other provided by the database user does not have any objects, just have to query all the table permissions, and therefore cannot do the data reverse. So decided to use materialized view, the data in the other database to take over, although the amount of data is large, but only once a month, and if set to incremental update, it will not be too slow. Now record the creation of materialized views (take a table for example).

First, preparation conditions and remarks

Suppose both sides of the database are oracle10g, need to synchronize the table called: Gg_zlx_zhu, the other database user name: username, password: password,sid:cpees.

Second, start working

1, first to create Db_link

CREATE DATABASE LINK to_cpees

CONNECT to 'username ' identified by "password"

Using "cpees"

Where Cpees is the new TNS to the other database. Execution, now we have created the Db_link to_cpees to the other database.

2. Create materialized view to refresh log quickly

As mentioned above, the refresh of the view will take the form of an incremental refresh, so to accommodate incremental refreshes, Oracle requires that the materialized view log be established on the live table.

CREATE materialized VIEW LOG on gg_zlx_zhu with PRIMARY KEY including NEW VALUES;

(The SQL above is to be executed on the remote database and cannot be executed locally)

3. Create materialized views

Materialized view, from the top of the name to open, it should belong to the view, but really materialized. Its materialization is for the normal view and there is no real physical storage, in fact, the materialized view can be simply regarded as a physical table (no specific explanation).

Create materialized view Gg_zlx_zhu --creating materialized view build IMMEDIATE--Create REFRESH FAST with PRIMARY after the view is written Key--based on primary table primary key incremental Refresh (FAST, incremental) on DEMAND--refreshes the ENABLE QUERY REWRITE when required by the user--read/write as SELECT * from [email protected]_cpees;--query statement

4. View Refresh

According to business needs, the monthly refresh, so can not be job, and a lot of, so also can not one refresh. Based on the above conditions, choose to refresh the materialized view using the Refresh method in the Oracle self-contained tool Dbms_mview Toolkit. The method has two parameters, the first parameter is the materialized view name that needs to be refreshed, and the second parameter is the refresh mode. We can write storage, call the Refresh method for each materialized view, or use "," to refresh the materialized view once it has been connected. As follows:

CREATE OR REPLACE procdure P_mview_refresh as

BEGIN

Dbms_mview. REFRESH ('gg_zlx_zhu ', ' f ');

END P_mview_refresh;

or use

CREATE OR REPLACE procdure P_mview_refresh as

BEGIN

Dbms_mview. REFRESH ('gg_zlx_zhu,gg_zlx_fu ', ' ff ');

END P_mview_refresh;

Attention:

1. If you need to refresh multiple materialized views at the same time, you must concatenate each materialized view name with a comma and

Each view indicates the refresh mode (f, incremental refresh, C, full refresh, or forced refresh).

2, log and materialized view after the creation, delete the logs, you need to recreate the materialized view, otherwise you cannot increment

Refresh.

3. Because the materialized view written above is updated according to the primary key, the primary table must have a primary key.

4, the above article in the red is replaceable, we can according to their own project needs to modify.

I hope the above content will be helpful to everyone.

Forgot to write Delete method, log and materialized view to be deleted separately

DROP materialized VIEW LOG on [email protected];

DROP materialized VIEW Gg_zlx_zhu;

Oracle materialized View