Oracle materialized View Fast update

Build a Test table

CREATE TABLE T_table nologging as SELECT * from Dba_objects;create table T_table_1 nologging as SELECT * from Dba_tables;

--Create a log record

Create materialized view Log on t_table with ROWID, sequence (Object_name,object_type,owner) including new Values;create MA terialized View Log on T_table_1 with rowid,sequence (table_name) including new values;

--Creating materialized views

Create materialized view mv_t_table nologging refresh fast on Demand with rowID START with To_date (' 21-08-2017 10:09:08 ', ' dd-mm-yyyy HH24:MI:SS ') NEXT Sysdate + 1/(24*60) as SELECT COUNT (*), a.object_type,a.owner from t_table a,t_table_1 b whe Re A.object_name=b.table_name GROUP by A.object_type,a.owner

--Test

SELECT * from mv_t_table where owner= ' system ';d elete from t_table where owner= ' SYSTEM '; insert to t_table select * from D ba_objects where owner= ' SYSTEM ';

--View Log generation amount

Select A.name, B.value from V$statname A, v$mystat b where a.statistic# = b.statistic# and A.name = ' redo size ';

--View Refresh Log

SELECT owner,mview_name,last_refresh_scn,last_refresh_date,query,revision from dba_mview_analysis WHERE owner= ' UTF32 ';

--View the Log record table, mlog$ is the log record table, once the update is complete, the contents of this table will be cleared out;

SELECT * FROM Mlog$_t_tableselect * from Mlog$_t_table_1;

Official note See: http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#REPLN265

This article is from the "Snowhill" blog, make sure to keep this source http://snowhill.blog.51cto.com/339421/1958119

Oracle materialized View Fast update