Oracle Materialize view using fast refresh method

--materialized view
--it's basically a snapshot we use to call in the past, unlike view the query result stored in a snapshot table.
--hence, the actual transaction table won't queried often affect the application performance.
--most commonly for reporting/business intelligence needs this is being used predominantly.

--types of materialized views are with different refresh types
--complete
--fast
--force

--the data collected in the mv table can be refreshed instantly or as needed...
--on commit...
--on demand

--the privilege required to created mv's are
--create materialized view
--create database link (in case remote table used across database)...

--let's discuss today about fast refresh with a use case.

--step 1
create materialized view log on employees
WITH PRIMARY KEY
INCLUDING NEW VALUES;

--step 2
create materialized view mv_employees
build immediate
refresh fast
on demand
as
select * from employees;

--step 3
update employees
set last_name = 'King'
where employee_id = 100;

INSERT INTO employees (
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
) VALUES (
950,
'test_fname',
'test_lname',
'SKING22',
'515.123.4567',
SYSDATE,
'AD_PRES',
24000,
NULL,
NULL,
90
);

COMMIT;

--Step 4
--incremental data capture here...
--latest update and insert records...
select * from mlog$_employees;

--Step 5
--since we set mv refresh on demand
--we need to maintain and trigger refresh operation...
begin
dbms_mview.refresh('mv_employees','F');
end;

--Step 6
--upon successful completion of step 5.
--the latest incremental data reflected under mv...
select * from mv_employees where employee_id in (100,950);

--drop materialized view log on employees;
--drop materialized view mv_employees;