materialized view.sql

-- complete refresh on commit
CREATE MATERIALIZED VIEW EMP_MV
REFRESH COMPLETE ON COMMIT
AS
SELECT E.EMPLOYEE_ID,E.FIRST_NAME,E.LAST_NAME,E.EMAIL,D.DEPARTMENT_NAME,E.SALARY,L.CITY 
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.DEPARTMENT_ID IS NOT NULL;

-- CREATE INDEX FOR MATERIALIZED VIEW
CREATE INDEX EMP_MV_INDEX ON EMP_MV(EMPLOYEE_ID);


SELECT * FROM EMP_MV; 
select * from emp_details_view; 


-- manually refresh
begin
  dbms_mview.refresh(list => 'EMP_MV');
end;

UPDATE EMPLOYEES E SET E.FIRST_NAME = 'Steve' where e.employee_id = 100; 

-- alter mv on demand
ALTER MATERIALIZED VIEW EMP_MV
REFRESH COMPLETE ON DEMAND
START WITH SYSDATE NEXT SYSDATE+(2/24*60);


-- create materilized view log
CREATE MATERIALIZED VIEW LOG ON EMP_MV WITH ROWID, SEQUENCE(EMPLOYEE_ID)
INCLUDING NEW VALUES; 

-- alter mv refresh fast
ALTER MATERIALIZED VIEW EMP_MV
REFRESH FAST ON COMMIT; 


-- DELETE MV
DROP MATERIALIZED VIEW EMP_MV;