Basic concepts and usage (concepts) of Plsql_material view materialized views (synchronizing tables between two databases in a materialized view or real-time backup of important tables)

2014-06-08 Baoxinjian

1. usage

A materialized view is a database object that includes a query result, which is a local copy of the remote data, or is used to generate a summary table based on the sum of the data tables.

Materialized views store data based on remote tables and can also be referred to as snapshots. For replication, materialized views allow you to maintain a copy of the remote data locally , which is read-only.

If you want to modify the local copy, you must use the Advanced Replication feature. When you want to extract data from a table or view, you can extract it from a materialized view.

For data warehouses, the materialized views created are typically aggregated views, single-table aggregation views, and connection views.

To achieve data synchronization between two databases, there can be a time difference.

1. How to Refresh

Fast

Complete

Fource

2. How to Refresh

Dbms_refresh. Refresh

Dbms_mview. Refresh

2. Specific Applications

(1). build mview log log file in source database

Create materialized view Log on W_1;

----Note: (Test is a table name or view name, a materialized view on a view, see materialized view based on a view

----Create materialized View statements:

(2). Create Materializad view syntax in statistics

Create materialized View Mv_test

----Mvtest for materialized view names

Build Immediate

The build data----created is the build deferred

Refresh Fast

----Incremental Refresh

On commit

----submitted when the base table is updated, this sentence is invalid for the view

With rowID

----here to create a materialized view based on ROWID, corresponding to the primary key

As

Select * from TEST;

----Generating materialized view data statements

(3). Refresh on Call

Dbms_refresh.refresh (' W_1 ')

3. Syntax

1. Basic syntax

4. Case Studies

Case: Create a materialized view of three refresh modes and create a log with Dbms_job timed refresh Mview Group

Step1. Create three entity tables

CREATETABLEBXJ_OBJECTS_T1 (owner,object_name, Subobject_name,object_idPRIMARYKEY, data_object_id, Object_type, created, Last_ddl_time,Timestamp, Status,Temporary, generated, secondary, namespace, Edition_name)AsSELECT*Fromall_objects;CREATETABLEBxj_objects_t2 (owner,object_name, Subobject_name,object_idPRIMARYKEY, data_object_id, Object_type, created, Last_ddl_time,Timestamp, Status,Temporary, generated, secondary, namespace, Edition_name)AsSELECT*from all_objects; CREATE TABLE bxj_objects_t3 (owner, object_name, Subobject_name, object_id PRIMARY KEY , data_object_id, Object_type, created, Last_ddl_time, timestamp, status, temporary, Generated, secondary, namespace, Edition_name)as SELECT * from all_objects;    

Step2. Create materialized view logs

On bxj_objects_t1;  Onbxj_objects_t3;  

From- mlog$_bxj_objects_t1 from mlog$_bxj_objects_t2 to mlog$_bxj_objects_t3 

Step3. Creating materialized views

CREATE materializedVIEW bxj_objects_mv_t1 REFRESH FAST as SELECT * from apps.bxj_objects_t1;  CREATE materialized VIEW bxj_objects_mv_t2 REFRESH Force as SELECT * from bxj_objects_t2;  CREATE materialized VIEW bxj_objects_mv_t3 REFRESH complete as SELECT * from bxj_objects_t3;
                        

Step4. Creating materialized views Refresh Group

EXEC Dbms_refresh. Make (' Rep_mviewgroup ', ' bxj_objects_mv_t1,bxj_objects_mv_t2,bxj_objects_mv_t3 ', sysdate, ' sysdate + 1 ')

Step5. Refresh materialized views

EXEC Dbms_mview.refresh ('bxj_objects_mv_t1','C');  EXEC Dbms_refresh.refresh ('rep_mviewgroup');          

Step6. Join Dbms_jobs to refresh materialized views at regular intervals

DECLAREJobnonumber;--begin  Dbms_job. SUBMIT (jobno,  ' dbms_refresh.refresh ( "| |  ' rep_mviewgroup||  ", -- Execute script sysdate, Span style= "color: #008080;" >-- now execute  sysdate+1< Span style= "color: #ff0000;" > ' 

Thanks and regards

Basic concepts and usage (concepts) of Plsql_material view materialized views (synchronizing tables between two databases in a materialized view or real-time backup of important tables)