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;
