qq_ forty-five million eight hundred and forty-nine thousand tw 2022-02-13 05:09:18 阅读数:392
–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;
copyright:author[qq_ forty-five million eight hundred and forty-nine thousand tw],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130509158810.html