Oracle materialized View

If we encounter the need to take data from the database of other systems for statistical analysis, the suspect chooses to use ORACLE 's ODI tool for the number of votes, but the other side of the database provided by the user does not have any objects, just have to query all the table permissions, so the data can not be reversed.

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

1. CREATE DATABASE LINK To_cpees
3. CONNECT to ' username ' identified by "password"
5. 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 Oracle materialized view Fast Refresh Log

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.

1. CREATE materialized VIEW LOG on Gg_zlx_zhu
3. With PRIMARY KEY
5. including NEW VALUES;

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

3. Create an Oracle materialized view

Oracle materialized view, from the top of the name to open, it should belong to the view, but it does 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).

1. Create materialized view Gg_zlx_zhu--creating materialized views
3. Build IMMEDIATE--created after the view has been written.
5. Refresh fast with PRIMARY key--refresh based on primary table primary key increment (FAST, incremental)
7. On DEMAND--refreshed by the user when required by the user
9. Enable QUERY REWRITE-readable and writable
11. As
13. SELECT * from [email protected]_cpees; --Query statements

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:

1. CREATE OR REPLACE procdure P_mview_refresh as
3. BEGIN
5. Dbms_mview. REFRESH (' Gg_zlx_zhu ', ' f ');
7. END P_mview_refresh;

or use

1. CREATE OR REPLACE procdure P_mview_refresh as
3. BEGIN
5. Dbms_mview. REFRESH (' Gg_zlx_zhu,gg_zlx_fu ', ' FF ');
7. 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

1. drop materialized view log on [email  Protected];  
2.  
3. Drop materialized view gg_ Zlx_zhu;