Subject: We will review here how to create materialized view and how to refresh the materialized view on demand.
Step1: Create Employee and Employee Address table based
on that we create Materialized view. Also, insert 1 -1 record each.
create table xx_emp (emp_num NUMBER, emp_name
VARCHAR2(240));
INSERT INTO xx_emp VALUES(1001,'David');
create table xx_emp_add (emp_num NUMBER, address
VARCHAR2(240));
INSERT INTO xx_emp_add VALUES(1001,'USA');
Step2: Create materialized view based on the above-created
custom tables.
create materialized view xx_emp_v AS
select
a.emp_num,
a.emp_name,
b.address
from
xx_emp a,
xx_emp_add b
where a.emp_num=b.emp_num;
Step3: Try to run the materialized view.
select * from xx_emp_v; --It will give one record only
Step4: Create a new Entry and check the materialized view
output.
INSERT INTO xx_emp VALUES(1002,'John');
INSERT INTO xx_emp_add VALUES(1002,'France');
select * from xx_emp_v;
--It will give one record only
Step5: We need to refresh the materialized view using the below script and check the Materialized view output.
exec dbms_mview.refresh('xx_emp_v'); -- Materialized view refresh script.
select * from xx_emp_v; --It will give two records only
