Interpreting Oracle's Day3

–COMPLETE Full refresh
–FAST Incremental updating
–FORCE Automatic selection

–ON COMMIT Submitting in the base table is to refresh the materialized view
–ON DEMAND Manually refresh

– Create a materialized view that is refreshed manually
create materialized view mv_address1 as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id

– Query materialized view
select * from mv_address1

– Insert data into the base table
insert into T_ADDRESS VALUES(9,‘ Xisi banner ’,2,2);
commit;
– Query base table data
select * from t_address

– Execute the following statement to refresh
begin
DBMS_MVIEW.refresh(‘MV_ADDRESS1’,‘C’);
end;

– Create a materialized view that refreshes automatically - The base table occurs commit operation , Refresh materialized view automatically
create materialized view mv_address2
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id

– Insert data into the base table
insert into T_ADDRESS VALUES(10,‘ In the west 5 flag ’,2,2);
commit;
– Query materialized view
select * from mv_address2

– Materialized views that do not generate data when created

create materialized view mv_address3
build deferred
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id

– Query materialized view
select * from mv_address3

insert into T_ADDRESS VALUES(12,‘ In the west 7 flag ’,2,2);
commit;
– The first refresh must be performed manually ,
begin
DBMS_MVIEW.refresh(‘MV_ADDRESS3’,‘C’);
end;

–. Create a materialized view with incremental refresh

– The premise is that you must create materialized view logs ： Record what changes have taken place in the base table , Use these records to update the materialized view

create materialized view log on t_address with rowid
create materialized view log on t_area with rowid

– Create statements in materialized views , There must be a base table ROWID

create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid,ar.rowid arrowid, ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id

select * from mv_address4
– insert data
insert into T_ADDRESS VALUES(14,‘ In the west 9 flag ’,2,2);
commit;
delete from t_address where id=13;
commit;
– Manually refresh
begin
DBMS_MVIEW.refresh(‘MV_ADDRESS4’,‘C’);
end;