Create a materialized view of automatic, incremental updates

Create a recurring incremental refresh materialized view

1, in the source library to establish a synchronized table materialized view log:

create materialized View Log on Db_sbzs.t_sb_yzpz tablespace m_view;

2, create materialized views in the target library to implement periodic incremental refreshes:

Create materialized View Db_jydjz.t_sb_yzpz

Refresh Fast

On demand

start with Sysdate

Next To_date (Concat (To_char (sysdate+1, ' dd-mm-yyyy '), ' 00:00:00 '), ' dd-mm-yyyy hh24:mi:ss ' )

With primary key

as

SELECT * from DB_SBZS.T_SB_YZPZ@DB_LINK_JYSJFZ     

where gljg_dm like ' 2445202% ' and lr_sj > Date ' 2011-10-1 ';

Select owner,segment_name,bytes/1024/1024 from dba_segments where tablespace_name= ' M_view ';

View The last time the view is refreshed view the view: Dba_mview_analysis

View materialized View Log view: Mlog$_tablename (tablename to actual table)

How to refresh materialized views with system packages:

begin
Dbms_mview.refresh (tab=> ' An_user_base_file_no_charge ',
Method=> ' FAST ',
PARALLELISM=>1);
End

materialized views Benefits: Optimizes view query speed. (Can be indexed), synchronizing data

disadvantage: Take up storage space

• Users who create materialized views need permissions: Create Materializedview, create TABLE, create VIEW, SELECT, if required to be created in other modes, need to have create any on the table Materialized view and Slect permissions. If you are querying materialized views in a reference to another schema, you need global query Rewriteor query rewrite permissions for the referenced table. If you intend to use materialized views, you also need to modify or add the query_rewrite_enabled option in the parameter file, and the value is true.