Interpreting Oracle's Day3

interpreting oracle day3 day

–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